Dyanmic SQL in Stored Proc

[jduckwo]

I am trying to get a return value from dynamically created SQL statement in a stored procedure so that the stored procedure can determine how to proceed. Any ideas on how to do this??

EXAMPLE CODE:
--------------------

CREATE PROCEDURE st_addHistory (@record_id VARCHAR(50))AS
DECLARE @strSQL VARCHAR(1024)
SELECT @strSQL = 'SELECT somefield FROM tblSomething WHERE id = ' + @record_id
EXEC(@strSQL)

/*I need the Value from [somefield] here so I can see if it has a value*/
If somefield = 'Test1' DO THIS
...do some other stuff
GO

--------------------
END CODE


Thanks for any help,
Jake

[sduggan]

Jake,
Perhaps you need to approach the problem from a different angle?:

It looks like the only part of the sql that is dynamic is the record_id. If this is true then maybe make the sp look like:

---------- Sample Pseudo Code – Start
CREATE PROCEDURE st_addHistory (@record_id VARCHAR(50)) AS
-- Validate @record_id so that sp does not break ...

-- Declare and Initialise a variable for output of somefield
declare @ouptutvar as somefieldtype
set @ouptutvar = ‘’

-- do ‘dynamic sql’
select @ouptutvar
from tblSomeTable
where id = @record_id

-- use resultant output value
if @ouptutvar = <something1>
begin
<do something>
end
if @ouptutvar = <something2>
begin
<do something2>
end
---------- Sample Pseudo Code - End

Cheers sduggan …