Return Value within Stored Procedure using Dynamic SQL
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....
But it keeps giving me errors because of the variables.
SELECT @MyValue = @MyDBField FROM @MyDBTable WHERE MyID=@N
I'd rather not have to create another stored procedure to output the value based on the parameters, but is that the only way?
Is the table that you are trying to fetch a value from a TEMP table created in the stored procedure?
If you are looking for a particular value,
SELECT columnA FROM #tempTable WHERE MyID=@N
What do you mean "only dynamic sql can be used with EXEC() and the like"?
SELECT columnA FROM #tempTable WHERE MyID=@N AND columnA IN (@MyValue)
Last edited by WolfShade; 06-05-2009 at 03:55 PM.
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...
Now in the next line I want to print the values in fields MyFld1, MyFld2, MyFld3. Is this possible?
EXEC('select MyFld1, MyFld2, MyFld3 FROM MyTable WHERE ID=1')
can you tell us what dbms you're using?
Oh.. wait a minnit.. do you mean a SP that has multiple queries, the first one setting variables that the others can use?
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread