Here is the problem:
DECLARE @variable smallint
DECLARE @mySQL varchar(100)
SET @mySQL = 'Dynamic SQL statement that produces a result set of one smallint value'
--THIS RETURNS A RESULT SET
--THIS WILL NOT PUT THE RESULT SET INTO THE VARIABLE
SET @variable = (EXECUTE(@mySQL))
I need the value produced by (EXECUTE(@mySQL)) to be stored in @variable that will be used in another dynamic SQL statement. How do I do this?