title: SQL Store a value from an EXECUTE Command into a variable
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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 
  </code></pre>
  
  <p>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</p>
  
  <pre><code>SET @SQLString = ' UPDATE PhoneNumbers SET Active = ''1'' WHERE
            PhoneNumber = ''' + @Number + ''';';
  EXEC(@SQLString);
  </code></pre>
  
  <p>If this is confusing in anyway, or you have questions, please ask. Any help is very much appreciated. Thanks</p>
  
  <p>Update #1:</p>
  
  <p>I have this new string now</p>
  
  <pre><code>@SQLString = 'SELECT PhoneNumber FROM ' + @Location ' + ' WHERE Room = ''' + @Room + ''';';
  
  EXECUTE SP_EXECUTESQL @SQLString
  </code></pre>
  
  <p>gets the correct number, but I don't know how to set up a OUTPUT parameter.</p>
  
  <p>I'm attempting to follow this example from Microsoft </p>
  
  <pre><code>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
  </code></pre>
  
  <p>But I don't see how their declaring the lastlNameOUT variables.</p>