Return Value within Stored Procedure using Dynamic SQL

[mataichi]

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....


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!

[WolfShade]

Is the table that you are trying to fetch a value from a TEMP table created in the stored procedure?
SELECT columnA FROM #tempTable WHERE MyID=@N
If you are looking for a particular value,
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"?

^_^

[mataichi]

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...
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?

[chazzy]

can you tell us what dbms you're using?

[mataichi]

Sql server 2005

[WolfShade]

Oh.. wait a minnit.. do you mean a SP that has multiple queries, the first one setting variables that the others can use?