Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Unanswered: sql as parameter or variable

    Lets say I do a Remote Procedure Call. So there is Server1 and Server2... How do I pass the results of a select statement as a parameter over to another stored procedure so it can use it...

    I really am going to need a short example as I don't have any clue and I need to see what is being done. I can't find examples of this anywhere. The example can be as simple as this (although my statements are going to be COMPLETELY botched cause I have NO IDEA what the statements should look like, but you should be able to follow.

    Create procedure procedure1 as
    declare @thevariable
    select * into @thevariable from tbl
    exec procedure2 @thevariable

    create procedure procedure2 as
    select * from @thevariable

    Something to the liking... something simple! If anyone could do that for me oh my god that would make my LIFE!!!!!!!! You have no idea how long I've been trying to figure out ways to approach this project and if something like this would work... EVERYTHING WOULD BE AMAZING!

  2. #2
    Join Date
    Oct 2002
    Location
    BC, Canada
    Posts
    17

    Re: sql as parameter or variable

    Hi there,

    You can use Select Into to save dataset into table, not variable, therefore you can not pass this variable as parameter. What you need is cursor that will call your second sp as many times as there are records in your table (from Select Inot statement). So it should look something like:

    Create Proc mk1sp_Proc1 As
    Declare @var varchar(50)
    Declare cur_MyCursor Cursor For
    Select MyValue From MyTable

    Open cur_MyCursor

    Fetch Next from cur_MyCursor Into @var

    While ( @@fetch_status <> -1 )
    Begin
    Exec mk1sp_Proc2 @var
    End

    Fetch Next from cur_MyCursor Into @var

    Close cur_MyCursor
    Deallocate cur_MyCursor
    Go


    Create Proc mk1sp_Proce2 as
    select * from @thevariable
    Go

    Good luck, Marin

    Originally posted by justastef
    Lets say I do a Remote Procedure Call. So there is Server1 and Server2... How do I pass the results of a select statement as a parameter over to another stored procedure so it can use it...

    I really am going to need a short example as I don't have any clue and I need to see what is being done. I can't find examples of this anywhere. The example can be as simple as this (although my statements are going to be COMPLETELY botched cause I have NO IDEA what the statements should look like, but you should be able to follow.

    Create procedure procedure1 as
    declare @thevariable
    select * into @thevariable from tbl
    exec procedure2 @thevariable

    create procedure procedure2 as
    select * from @thevariable

    Something to the liking... something simple! If anyone could do that for me oh my god that would make my LIFE!!!!!!!! You have no idea how long I've been trying to figure out ways to approach this project and if something like this would work... EVERYTHING WOULD BE AMAZING!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •