Using Variables in SQL Statment

LJG
LJG used Ask the Experts™
on
Question - How do I use a variable for a table in the below SQL
      (The below errors out on -->  set @vcSQL)
      declare @intNewRecords_OutPut AS int
      declare  @vcTemp_Table  as varchar(50), @vcSQL  AS  varchar(max)      
      set @vcTemp_Table= 'tbl_zr_Invoice_LineItems_Add_Products'
      set @vcSQL= 'Select ' +  @intNewRecords_OutPut + ' = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products'
      exec (@vcSQL)
      print @intNewRecords_OutPut

This Works
      declare @intNewRecords_OutPut AS int
      Select  @intNewRecords_OutPut = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products
      print @intNewRecords_OutPut

Why do I want to do this
I have a stored proc that I use a temp table (#tbl_Temp).  But in case I want to do some debugging in the future, I want to be able to use a real table.  Hence send a parameter that would allow me to change tables on the above.  I know I can use an If statement with two statements, but I like to know the above.

Thanks in advance for any help.
LJG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hello,

you need to cast @intNewRecords_OutPut as varchar to use it in this line of code
set @vcSQL= 'Select ' +  ...

Open in new window

LJG

Author

Commented:
I don't understand the below line give me an error

set @vcSQL= 'Select  ' +  @intNewRecords_OutPut + ' = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products'

Commented:
Here is a code that should work (with output parameter):
      declare @intNewRecords_OutPut AS int
      declare  @vcTemp_Table  as varchar(50), @vcSQL  AS  varchar(max)    
DECLARE @ParmDefinition nvarchar(500)   
      set @vcTemp_Table= 'tbl_zr_Invoice_LineItems_Add_Products'
      set @vcSQL= 'Select @intNewRecords = Sum(QuantitySold) from  tbl_zr_Invoice_LineItems_Add_Products'
      SET @ParmDefinition = N'@intNewRecords int OUTPUT'
      EXECUTE sp_executesql @vcSQL, @ParmDefinition, @intNewRecords = @intNewRecords_OutPut OUTPUT
      print @intNewRecords_OutPut

Open in new window

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

LJG

Author

Commented:
Thanks  pmoschk for working with me.

I get the following error with your exact code.

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 [Batch Start Line 0]
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Commented:
declare @vcSQL  AS  nvarchar(max)
LJG

Author

Commented:
That is already in line 2 of your code

 declare  @vcTemp_Table  as varchar(50), @vcSQL  AS  varchar(max)
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
The difficulty with dynamic SQL like that is passing variables.

Thats why SP_EXECUTESQL was created, so you can pass parameters....

Have a look at
DECLARE @intNewRecords_OutPut AS int
DECLARE @vcTemp_Table  as nvarchar(50)
DECLARE @vcSQL  AS  nvarchar(max)       
DECLARE @vcDeclare nvarchar(500)  

SET @vcTemp_Table= 'tbl_zr_Invoice_LineItems_Add_Products'
SET @vcSQL = N'SELECT @intNewRecords_Out  = Sum(QuantitySold) from '+@vcTemp_Table   

SET @vcDeclare = N' @intNewRecords_Out INT OUTPUT'   -- a declarative / parameter to be used inside sp_executesql which we will pass back as OUTPUT parameter.

EXECUTE sp_executesql @vcSQL, @vcDeclare, @intNewRecords_Out=@intNewRecords_OutPut OUTPUT  

print @intNewRecords_OutPut 

Open in new window


Takes a bit of getting used to, but is much more flexible. Have a read of https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017
LJG

Author

Commented:
Thanks so much for the code and the link.  

So at 68 after 40 years of programming I'm still learning

Guess I will be until I die :)
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
We can always learn... I know I am still learning :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial