0

I have a query that inside in quotes:

DECLARE @PivotQuery AS NVARCHAR(MAX),
        @PivotColumns AS NVARCHAR(MAX),
        @PivotNull AS NVARCHAR(MAX)
SELECT @PivotColumns= ISNULL(@PivotColumns + ',','') + QUOTENAME(DepId)
FROM (SELECT DISTINCT sp.DepId FROM SKDEP sp) AS Stock
SELECT @PivotNull 
    = ISNULL(@PivotNull + ',','')
    + 'ISNULL(' + QUOTENAME(DepId) + ', 0) AS '
    + QUOTENAME(DepId)
FROM (SELECT DISTINCT sp.DepId FROM SKDEP sp) AS Stock
SET @PivotQuery =
N'SELECT P.P, P.D, '+@PivotNull+'
FROM
        (
            select 
                s.prdid as P,
                p.prddsc as D,
                case when mstes=''S'' then sum(-MstCntDisp) else sum(MstCntDisp) end as Stock,
                s.DepId
                from SKMOV S
                join PRODUC P on s.PrdId=p.PrdId
                where s.mstsal>0 AND s.MstTpo=''S'' and s.PrdId=**''*TF033''**
                group by s.prdid, p.PrdDsc, s.MstES, s.depid
        ) AS t
PIVOT(sum(Stock)
FOR DEPID IN (' + @PivotColumns + ')) AS P'
EXEC sp_executesql @PivotQuery

In the where clause I need specific my parameters with the question mark, so:

and s.PrdId=?

But I don't know how to specify the parameter when having the query in quotes, because it doesn't take it.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Federico Martinez
  • 65
  • 7
  • 1
    The [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15) contains [examples](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver15#a-executing-a-simple-select-statement) on how to parametrise dynamic statements. – Thom A Dec 17 '21 at 18:38
  • 1
    Does this answer your question? [EXEC sp_executesql with multiple parameters](https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters) – Thom A Dec 17 '21 at 18:39
  • @Larnu but my problem is with how to pass the parameter in BIRT having the query in quotes. – Federico Martinez Dec 17 '21 at 18:44
  • 1
    Both links explain how to pass a parameter to a dynamic statement. – Thom A Dec 17 '21 at 19:01
  • @Larnu What I don't understand is how to apply that to the BIRT parameter. – Federico Martinez Dec 17 '21 at 19:06
  • 1
    Assign it to a variable outside the dynamic statement, and then pass said variable as a parameter. – Thom A Dec 17 '21 at 19:36
  • Does this answer your question? [EXEC sp\_executesql with multiple parameters](https://stackoverflow.com/questions/28481189/exec-sp-executesql-with-multiple-parameters) – Charlieface Dec 18 '21 at 18:33

1 Answers1

0

Finally i resolved my problem that this way:

In the where clause, im pass value of one string,

where s.mstsal>0 AND s.MstTpo=''S'' and s.PrdId=''PRODUCT_ID''

And after when report open (in tab Script/beforeOpen), im replace the string value with the parameter.

this.queryText=this.queryText.replace("PRODUCT_ID",params["ID"].value);

And works!

Federico Martinez
  • 65
  • 7
  • 1
    While this works, it is open to SQL injection attacks unless you can be absolutely shure that the input parameter is clean. – hvb Dec 20 '21 at 07:16