CODEWITHSUNDEEP
sqlsql-serverif-statementstored-proceduresparameters
Bernard
Bernard

Reputation: 21

SQL - If Parameters within Stored Procedure

I am trying to get a complex stored procedure to work, but I can't figure out the relationship between stored procedures, parameters and IF/THEN statements.

I have stripped my code down to the attached chunk of code, that shows where I am going wrong. When I execute it, I get an error

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'and'

Basically, I have 3 variables - @Test1, @Test2 and @Test3. These are prompts that appear when you execute the stored procedure. If the user enters '1', '2', '3' respectively, it should show the text 'Show 1 and 2 and 3 Plus Pivot Section'. If the only enter '1','2' it should show the text 'Show 1 and 2 Plus Pivot Section'.

Below is my code, am I doing something wrong??

ALTER PROCEDURE [dbo].[sp_Data_Extract_Test]
   @Test1 [NVARCHAR] (400),
   @Test2 [NVARCHAR] (400),
   @Test3 [NVARCHAR] (400)
WITH EXECUTE AS CALLER
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @Output NVARCHAR(400) ;
   Declare @param NVARCHAR(900) ;

   Set @param = '@Test1 nvarchar(400), @Test2 nvarchar(400), @Test3 nvarchar(400)'

   if (@Test1 = '1')
   BEGIN
      SET @Output = 'Show 1';
   END

   if (@Test2 = '2')
   BEGIN
      SET @Output = @Output + ' and 2';
   END

   if (@Test3 = '3')
   BEGIN
      SET @Output = @Output + ' and 3';
   END

   BEGIN
      SET @Output = @Output + ' Plus Pivot Section'
      PRINT @Output
   END

   EXECUTE SP_EXECUTESQL @Output, @param, @Test1, @Test2, @Test3;
END

Edit: My example above isn't a good example. I was trying to narrow it down to identify the problem, but it seems as though the Execute statement at the bottom expects a SQL statement, not a line of text.

What I am trying to do, is effectively the following. I want to execute a stored procedure that pivot's a data set. The data is built up by 2 appends (unions) that are based on prompts. I want it so that the user can potentially run it for 1 type of Quantity, for 2, or for 3. If they want to see all 3 Quantities, it needs to do 2 appends. The code is as below. I've added XXX's where the SQL statement is because I know those parts are working fine, it's only when I try to do this if statement based on variables so I can potentially filter off the unions.

--- Above: Alter Stored Procedure, with variable declarations
declare @V1 NVARCHAR(4000) ;
declare @param NVARCHAR(900) ;

set @param='XXXXXX'

if (@Value1 = 'Qty_Ordered')
BEGIN
set @V1='SELECT XXXXXX’
END

if (@Value1 = 'Qty_Ordered' and @Value2 = 'Qty_Supplied')
BEGIN set @V1= @V1 + '
UNION ALL
SELECT XXXXX'
END

if ((@Value1 = 'Qty_Ordered' or @Value2 = 'Qty_Supplied') and @Value3 = 'Qty_Forecast')
BEGIN set @V1= @V1 + 
'UNION ALL
SELECT XXXXXX’
END 

BEGIN 
set @V1 = @V1 + ' 
PIVOT
(
SUM([Value])
FOR p.forecast_Week_No IN ('+@cols1+','+@cols2+','+@cols3+')) AS pvt'
END

EXECUTE SP_EXECUTESQL @V1, @param, XXXXXXXX;

END

Upvotes: 2

Views: 8375

Answers (1)

user1897277
user1897277

Reputation: 495

The frist paramenter for sp_executesql should be a TSQL statement, but @Output does not represent that. If your intention was only to print the message then comment EXECUTE SP_EXECUTESQL @Output, @param, @Test1, @Test2, @Test3;


Now regarding Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'and'

sqlserver basically checked for syntax error in the TSQL stmt passed through @Output,
what it got was "Show 1 and 2 and 3 Plus Pivot Section"
As SHOW is not a SQL stmt, it considered it as a stored proc,
next it looked at 1, and 1 it is allowed as a paramenter
next it got "and", it is not accepted to used words like "and" while executing a SP
so gave a syntax error Msg 156, Level 15, State 1...

Upvotes: 1

Related Questions