AXISHK
asked on
Pass multiple parameter to TSQL
Passing multiple value into a parameter but it can't return any records. No idea what's wrong. Any idea ?
exec MyStorePro 'P000042545,P000160279'
CREATE PROCEDURE [dbo].[MyStorePro] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.INVENTD2ID, D.INVENTD3ID, D.INVENTD4ID, D.INVENTDGOLDTYPEID, REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END AS STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
+SUBSTRING(A.COSTGROUPID,4 ,3)+CAST(C AST(SUM(OU TQTY) AS decimal(20,2)) AS NVARCHAR(10)) Else '' END AS Material
into #TM1 FROM .LASTWIPMATERIALTRANS A
INNER JOIN .BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPI D
INNER JOIN .INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBA TCHID AND C.DATAAREAID='OPR'
INNER JOIN .BOMVERSION D ON C.REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
WHERE A.INVENTBATCHID in (''' + @INVENTBATCHID + ''')
GROUP BY SUBSTRING(A.COSTGROUPID,4, 3),CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END,
A.COSTGROUPID,A.INVENTBATC HID,D.ITEM ID,D.INVEN TD2ID,D.IN VENTD3ID,D .INVENTD4I D,D.INVENT DGOLDTYPEI D,
REFBOMID
exec MyStorePro 'P000042545,P000160279'
CREATE PROCEDURE [dbo].[MyStorePro] @INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT A.INVENTBATCHID,D.ITEMID ,D.INVENTD2ID, D.INVENTD3ID, D.INVENTD4ID, D.INVENTDGOLDTYPEID, REFBOMID,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END AS STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
CASE WHEN A.COSTGROUPID LIKE 'ST%' THEN CAST(CAST(SUM(OutQtySec) AS decimal) AS NVARCHAR(10))
+SUBSTRING(A.COSTGROUPID,4
into #TM1 FROM .LASTWIPMATERIALTRANS A
INNER JOIN .BOMCostGroup B ON B.COSTGROUPID=A.COSTGROUPI
INNER JOIN .INVENTBATCH C ON A.INVENTBATCHID=C.INVENTBA
INNER JOIN .BOMVERSION D ON C.REFBOMID=D.BOMID AND C.ITEMID=D.ITEMID AND D.DATAAREAID='OPR'
WHERE A.INVENTBATCHID in (''' + @INVENTBATCHID + ''')
GROUP BY SUBSTRING(A.COSTGROUPID,4,
A.COSTGROUPID,A.INVENTBATC
REFBOMID
Hi,
You now are selecting records into one temp table.
1) Try to remove "into ..." clause
2) Also directly run the statement to ensure it can really capture records.
You now are selecting records into one temp table.
1) Try to remove "into ..." clause
2) Also directly run the statement to ensure it can really capture records.
Hi AxisHK,
Just an issue with quotes within variable.
Please check below code.It will work for you.
Just an issue with quotes within variable.
Please check below code.It will work for you.
CREATE PROCEDURE [dbo].[MyStorePro]
@INVENTBATCHID VARCHAR(3000)
AS
BEGIN
SELECT a.inventbatchid,
d.itemid ,
d.inventd2id,
d.inventd3id,
d.inventd4id,
d.inventdgoldtypeid,
refbomid,
CASE
WHEN a.costgroupid NOT LIKE 'ST%' THEN 4
ELSE b.stonetype
END AS stonetype,
CASE
WHEN a.costgroupid NOT LIKE 'ST%' THEN Sum(outqty)
ELSE 0
END AS goldcost,
CASE
WHEN a.costgroupid LIKE 'ST%' THEN Cast(Cast(Sum(outqtysec) AS DECIMAL) AS NVARCHAR(10)) +Substring(a.costgroupid,4,3)+Cast(Cast(Sum(outqty) AS DECIMAL(20,2)) AS NVARCHAR(10))
ELSE ''
END AS material
INTO #tm1
FROM .lastwipmaterialtrans A
INNER JOIN .bomcostgroup B
ON b.costgroupid=a.costgroupid
INNER JOIN .inventbatch C
ON a.inventbatchid=c.inventbatchid
AND c.dataareaid='OPR'
INNER JOIN .bomversion D
ON c.refbomid=d.bomid
AND c.itemid=d.itemid
AND d.dataareaid='OPR'
WHERE a.inventbatchid IN (' + @INVENTBATCHID + ')
GROUP BY Substring(a.costgroupid,4,3),
CASE
WHEN a.costgroupid NOT LIKE 'ST%' THEN 4
ELSE b.stonetype
END,
a.costgroupid,
a.inventbatchid,
d.itemid,
d.inventd2id,
d.inventd3id,
d.inventd4id,
d.inventdgoldtypeid,
refbomid
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Tks
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN 4 ELSE B.STONETYPE END AS STONETYPE,
CASE WHEN A.COSTGROUPID NOT LIKE 'ST%' THEN SUM(OUTQTY) ELSE 0 END AS GoldCost,
Do you want the similar CASE WHEN two times?