SQL Store a value from an EXECUTE Command into a variable

Question

I'm attempting to store a value into a variable from a EXECUTE command. I know I'm suppose to use sp_executesql command, but all examples online are only making more confused. So here is what I'm trying to do.

I have a stored procedure that accepts two parameters (a table name, a room #). To have a dynamic table name, I use dynamic SQL style while using strings. I'm attempting to store a phone number that is either from multiple tables. I got this working so far.

DECLARE @Location   varchar(MAX);
DECLARE @Room       varchar(10);
DECLARE @Number     char(8);
DECLARE @SQLString  varchar(MAX);

SET @Location = N'CMPhone.dbo.GardenCottage';
SET @Room = N'202';

SET @SQLString ='SET @Number = (SELECT PhoneNumber FROM ' + @Location + '     WHERE Room = ''' + @Room + ''');';
PRINT(@SQLString);

OUTPUT
SET @Number = (SELECT PhoneNumber FROM CMPhone.dbo.GardenCottage WHERE Room = '202');

SET @Number = (SELECT PhoneNumber FROM CMPhone.dbo.GardenCottage WHERE Room = '202');
PRINT(@Number);

OUTPUT
123-4567

Which is the correct number. Now, here is where the problem comes in. I need to do another query using dynamic SQL so I can use multiple tables again. So in my stored procedure, I need to store my EXEC(@SQLString) into a variable (@Number) so I can use that value, and that's where I'm having problems. I can't get sp_executesql to store the value into @Number. The other query will look something like this

SET @SQLString = ' UPDATE PhoneNumbers SET Active = ''1'' WHERE
          PhoneNumber = ''' + @Number + ''';';
EXEC(@SQLString);

If this is confusing in anyway, or you have questions, please ask. Any help is very much appreciated. Thanks

Update #1:

I have this new string now

@SQLString = 'SELECT PhoneNumber FROM ' + @Location ' + ' WHERE Room = ''' + @Room + ''';';

EXECUTE SP_EXECUTESQL @SQLString

gets the correct number, but I don't know how to set up a OUTPUT parameter.

I'm attempting to follow this example from Microsoft

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
               FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                    @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

But I don't see how their declaring the lastlNameOUT variables.

You can do what I think you're attempting....with sp_executesql and an *out* parameter. Was there another reason you had to discard that? — David W, Jul 08 '15 at 18:43
No, I know that I need to use sp_execturesql and an out parameter, but that is what is giving me the trouble. I don't believe I'm using it correctly. The example's I keep finding are very complicated and don't offer much information on how to use them. — user3268054, Jul 08 '15 at 18:44
Also on the injection, I know it's not the best design. I'm fairly new to SQL, so I'm just doing this small project to learn and practice. Once I get it working, i know i'm going to have to go back and verify everything. — user3268054, Jul 08 '15 at 18:48
Related to the issue found here https://stackoverflow.com/questions/7229070/how-to-set-value-to-variable-using-execute-in-t-sql — Random Developer, Jul 08 '15 at 18:50
It sounds like you have a messed up table structure. Whenever you have multiple tables with the same structure (whatever `@location` refers to), then you have a suspicious data layout. — Gordon Linoff, Jul 08 '15 at 19:12
Yes @Gordon! Sounds a lot like [this issue](http://dba.stackexchange.com/questions/106215/problem-locating-rows-with-dynamic-sql) (see my comments). — Aaron Bertrand, Jul 08 '15 at 19:25
This is almost an exactly duplicate of http://dba.stackexchange.com/questions/106215/problem-locating-rows-with-dynamic-sql. However, we cannot close duplicate question across sites. — Gordon Linoff, Jul 08 '15 at 23:46

score 0 · Answer 1 · answered Jul 08 '15 at 23:34

use output variable in your EXECUTE sp_executesql like this:

EXECUTE sp_executesql @SQLString, N'@Number char(8) out',@Number out then you will get @Number value from inside dynamc sql, then you can use that value in other part of the query. hope this helps

SQL Store a value from an EXECUTE Command into a variable

1 Answers1