-
Dyanmic SQL in Stored Proc
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
-
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 …
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|