Query on EXECUTE sp_executesql

Question

I was trying to have the @sqlstring to run 3 times based on the months available from other file and these results all to be union to #table.

When I included the "memberid in ('00001', '00002','00003')", the error message is "Incorrect syntax near '00001'".
When I excluded the memberid, the error message is "Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1 [Batch Start Line 1268] Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'."

How can I fix the following query?

DECLARE @Month VARCHAR(2)
DECLARE @Month1 VARCHAR(2)
DECLARE @Month2 VARCHAR(2)
DECLARE @Month3 VARCHAR(2)
DECLARE @Year VARCHAR(4)
DECLARE @Year1 VARCHAR(4)
DECLARE @Year2 VARCHAR(4)
DECLARE @Year3 VARCHAR(4)
DECLARE @SQLSTRING NVARCHAR(MAX)

SET @MONTH1 = N'SELECT MONTH FROM #period WHERE ROW = 1' 
SET @MONTH2 = N'SELECT MONTH FROM #period WHERE ROW = 2' 
SET @MONTH3 = N'SELECT MONTH FROM #period WHERE ROW = 3' 
SET @YEAR1 = N'SELECT YEAR FROM #period WHERE ROW = 1' 
SET @YEAR2 = N'SELECT YEAR FROM #period WHERE ROW = 2' 
SET @YEAR3 = N'SELECT YEAR FROM #period WHERE ROW = 3' 

SET @SQLSTRING = N'select memberid, sales, @MONTH as month, @YEAR as svyear
from member@Month where month = @month and memberid in ('00001', '00002','00003')'


INSERT INTO #table
EXECUTE sp_executesql @SQLSTRING, @MONTH=@MONTH1, @YEAR=@YEAR1
INSERT INTO #table
EXECUTE sp_executesql @SQLSTRING, @MONTH=@MONTH2, @YEAR=@YEAR2
INSERT INTO #table
EXECUTE sp_executesql @SQLSTRING, @MONTH=@MONTH3, @YEAR=@YEAR3;

Why do you DECLARE the variables as a varchar(2) (or (4)), but then try to SET their values as an nvarchar that is far larger than 2/4 characters? — Larnu
You also never assign the values of @Month and @Year. Perhaps sample data and expected results will help us understand better what your real goal is here. — Larnu
As a hint, before trying to execute the statement, help yourself by just SELECTing the variables SQLSTRING, MONTH1 and YEAR1. I think that might make it obvious to you, why this is going wrong. — Jonathan Willcock
@Larnu, there are two columns in #period, year and month (in numeric). There will be only three rows of data in it. I would like to extract data from tables 'member_#month' based on the values appeared in #period. Let say if month 1 and 2 appeared in #period, then I need to extract data from table member_1 and member_2. — user5193498
You cannot prepare parts of query string as parameters. You need to build entire query as string - you have no values to pass as parameters. Yes, SQL injection warnings apply. — Arvo

ibram ibram · Accepted Answer · 2019-10-08T10:20:13

First of all you have to create your temp table. Your insert into temp table has to be in your sql query.

Then use your query with the escape character like this:

SET @SQLSTRING = N'INSERT INTO #table
select memberid, sales, @MONTH as month, @YEAR as svyear
from member where month = @MONTH and memberid in (''00001'', ''00002'',''00003'')'

and finally call the function:

DECLARE @sParams nvarchar(512) = '@MONTH VARCHAR(2), @YEAR VARCHAR(4)';

EXEC sp_executesql @SQLSTRING, @sParams, @MONTH, @YEAR;

Query on EXECUTE sp_executesql

1 Answers