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
– David W, Commented Jul 8, 2015 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
– user3268054, Commented Jul 8, 2015 at 18:44
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
– Gordon Linoff, Commented Jul 8, 2015 at 19:12
This is almost an exactly duplicate of dba.stackexchange.com/questions/106215/…. However, we cannot close duplicate question across sites. — Gordon Linoff
– Gordon Linoff, Commented Jul 8, 2015 at 23:46

user3583912 · Accepted Answer · 2015-07-08 23:34:43Z

0

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

answered Jul 8, 2015 at 23:34

user3583912

1,3321 gold badge17 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Store a value from an EXECUTE Command into a variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related