cancel
Showing results for 
Search instead for 
Did you mean: 

How to add parameter into query

Former Member
0 Kudos

select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP] from ocrd t0 inner join [DEV].[dbo].[2yearbycarditem] t1 on t0.cardcode = t1.cardcode

WHERE t1.ItemCode like '12V%'

group by t0.cardcode, t1.itemcode, t1.[year], t1.[month]

order by t0.cardcode, t1.[year],t1.[month]

how do i change this so the itemcode is a parameter. They need to enter an itemcode that starts with e.g. 12V

I have tried

WHERE t1.ItemCode like '%' + @itemcode + '%'

this does not work.

please help

thanks in advance- Janice

Accepted Solutions (0)

Answers (3)

Answers (3)

KennedyT21
Active Contributor
0 Kudos

Hi Janice Middleton....

Try This, If you want to pull all items the use % in the selection

/* select * from oitm x1 */ DECLARE @d1 AS nvarchar(100)

SET @d1=/* x1.ItemCode */ '[%0]'

SELECT t0.cardcode,

   t1.itemcode,

   t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]

FROM ocrd t0

INNER JOIN [DBO].[2yearbycarditem] t1 ON t0.cardcode = t1.cardcode

INNER JOIN oitm oi ON oi.ItemCode=t1.itemcode

WHERE oi.ItemCode LIKE @d1

GROUP BY t0.cardcode,

   t1.itemcode,

   t1.[year], t1.[month]

ORDER BY t0.cardcode,

   t1.[year],t1.[month]


Regards

Kennedy

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]

from ocrd t0 inner join [dbo].[@2yearbycarditem] t1 on t0.cardcode = t1.cardcode

WHERE t1.ItemCode  =[%0]

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Janice Middleton,

   Use following query, i included OITM table and removed dev db reference.

/* select * from oitm x1 */ 

declare @d1 as nvarchar(100)

set @d1=/* x1.ItemCode */ '[%0]'

select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]

from ocrd t0

inner JOIN [2yearbycarditem] t1 on t0.cardcode = t1.cardcode

  INNER JOIN oitm oi ON oi.ItemCode=t1.itemcode

WHERE oi.ItemCode like @d1

group by t0.cardcode, t1.itemcode, t1.[year], t1.[month]

order by t0.cardcode, t1.[year],t1.[month]

 

With regards,

Venki .A