Using Variable with Dynamic SQl

Question

Hi I am using Dynamic Query in My store Procedure Using following syntax

`   

    set @query =N'Select  ComponentName, '+ @cols +' From (
    Select Sum('+ @Sum_cols +') As Comp_stock,Com.ComponentName ,BB.BB_Name  
    from Z_DM_DR_CM_STOCK COM
    Inner Join BLOOD_BANK_MASTER BB on COM.BB_srno =BB.BB_SRNO
    where Com.Trans_date ='''+ Convert(Varchar(10), @Trans_Date,126) +'''
    AND   (  @BG  = ''''OR  COM.BGName =''' + @BG +'''

    )Group by Com.ComponentName,BB.BB_Name  ' +
    ')As sourcetable 
    PIVOT
    (
    SUM([Comp_stock]) FOR [BB_Name] IN ('+ @cols +')
    ) As PIVOTTABLE order by ComponentName'

  execute(@query)`

BUt its giving error

Must declare the scalar variable "@BG".

and @BG is Input Parameter

Can any one help?

AND ( @BG = ''''OR COM.BGName =''' + @BG +''' here @BG is included in string — Jay Shankar Gupta
– Jay Shankar Gupta, Commented Mar 9, 2018 at 10:29

Thom A · Accepted Answer · 2018-03-09 10:49:16Z

2

You have the line AND ( @BG = ''''OR COM.BGName =''' + @BG +'''. `@BG' isn't declared in your Dynamic SQL. You need to pass it like you did later in the same line:

AND   ( '' +  @BG + '' = '''' OR  COM.BGName =''' + @BG +'''

This query, however, appears to be open to SQL injection; you would be much better off parametrising it:

SET @query = N'
SELECT ComponentName,
        '+ @cols + N'  --This might need to be changed as well, but i don''t know how you''re generating this and I''m not guessing
FROM (SELECT SUM(('+ @Sum_cols + N') AS Comp_stock, --This might need to be changed as well, but i don''t know how you''re generating this and I''m not guessing
             Com.ComponentName,
             BB.BB_Name  
      FROM Z_DM_DR_CM_STOCK COM
           INNER JOIN BLOOD_BANK_MASTER BB ON COM.BB_srno =BB.BB_SRNO
      WHERE Com.Trans_date = @sTrans_date
        AND (@sBG = '''' OR COM.BGName = @sBG )
      GROUP BY Com.ComponentName,
               BB.BB_Name) AS sourcetable 
PIVOT (
    --This might need to be changed as well, but i don''t know how you''re generating this and I''m not guessing
    SUM([Comp_stock]) FOR [BB_Name] IN ('+ @cols + N')) AS PIVOTTABLE
ORDER BY ComponentName';

EXEC sp_executesql @query, N'@sBG int, @sTrans_date date', @sBG = @BG, @sTrans_Date = @trans_date; --I have guessed your data types

Note (as I know people have a habit of not reading comments people leave for them in their code) I have no idea what the data type for @BG and @Trans_date is, thus I have geussed they are int and daterespectively. You'll need to changed this, if i have guessed incorrectly.

You can see from the comments that you need to looks at how you add the values of the columns to your query as well; the way you've done it may also be open to injection, but i haven't seen the earlier part of your query.

edited Mar 9, 2018 at 10:49

answered Mar 9, 2018 at 10:33

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

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

4 Comments

Gans Over a year ago

'@BG'is varchar

Thom A Over a year ago

@Gans ok, You then you'll simply need to change the declaration for @sBG from an int to a varchar(??????) (?????? because you didn't state the size, and a varchar(1) is silly (use a char(1)).

Gans Over a year ago

Procedure expects parameter '@params' of type 'ntext/nchar/nvarchar'.

Thom A Over a year ago

That error message is very easy to fix... I (accidently) forgot to set @params as a nvarchar (it was a varchar. I've fixed this, but you should know how to troubleshoot code yourself.

Collectives™ on Stack Overflow

Using Variable with Dynamic SQl

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related