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.
CodePudding user response:
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!