www.webdeveloper.com
Results 1 to 6 of 6

Thread: Return Value within Stored Procedure using Dynamic SQL

  1. #1
    Join Date
    Oct 2006
    Posts
    93

    Return Value within Stored Procedure using Dynamic SQL

    Hi,
    I am trying to fetch a value from a table within a stored procedure using dynamic sql. Seems that only dynamic sql can be used with EXEC() and the like.
    Basically I want to do this....


    Code:
    SELECT @MyValue = @MyDBField FROM @MyDBTable WHERE MyID=@N
    But it keeps giving me errors because of the variables.

    I'd rather not have to create another stored procedure to output the value based on the parameters, but is that the only way?

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Is the table that you are trying to fetch a value from a TEMP table created in the stored procedure?
    Code:
    SELECT columnA FROM #tempTable WHERE MyID=@N
    If you are looking for a particular value,
    Code:
    SELECT columnA FROM #tempTable WHERE MyID=@N AND columnA IN (@MyValue)
    What do you mean "only dynamic sql can be used with EXEC() and the like"?

    ^_^
    Last edited by WolfShade; 06-05-2009 at 04:55 PM.

  3. #3
    Join Date
    Oct 2006
    Posts
    93
    I am new to writing stored procedures and somewhat confused, but from what I've researched, seems like if I want to do dynamic sql I have to use either EXEC() or sp_executesql.

    Can I access a field value within the stored procedure after an EXEC()?
    For instance say this is a line in my stored procedure...
    Code:
    EXEC('select MyFld1, MyFld2, MyFld3 FROM MyTable WHERE ID=1')
    Now in the next line I want to print the values in fields MyFld1, MyFld2, MyFld3. Is this possible?

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    can you tell us what dbms you're using?

  5. #5
    Join Date
    Oct 2006
    Posts
    93
    Sql server 2005

  6. #6
    Join Date
    Dec 2002
    Location
    St. Louis, MO, USA
    Posts
    1,582
    Oh.. wait a minnit.. do you mean a SP that has multiple queries, the first one setting variables that the others can use?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles