How to use table value parameter in Stored Procedure 2016

Question

The below stored Procedure use multiple temp table to get output. The WHERE clause are currently hard coded.

How can I make the values pass in where clause to be dynamic and pass as table value parameter

I am not sure if multiple select with where clause can be make dynamic.

Code :

CREATE PROC usp_product
AS
BEGIN
SET NOCOUNT ON;

DROP TABLE IF EXISTS TEMPA
DROP TABLE IF EXISTS FIRSTTAB
DROP TABLE IF EXISTS SECTAB
DROP TABLE IF EXISTS THIRDTAB
DROP TABLE IF EXISTS TWOYEARDATA
DROP TABLE IF EXISTS FOURTHTAB

SELECT DISTINCT * INTO TEMPA 
FROM SOCK AS I LEFT OUTER JOIN [dbo].[PLUG] AS M 
ON I.IPROD = M.BPROD 

SELECT DISTINCT
IPROD,
IITYP,
IMSSPC

INTO FIRSTTAB
FROM TEMPA WHERE ICLAS in ('01','02','03','04','13','14') and IID='IM' 
AND IPFDV NOT IN ('AB','ME')  

SELECT DISTINCT 
I.IPROD,
I.IITYP,
IMSSPC
INTO SECTAB

FROM SOCK AS I LEFT OUTER JOIN [dbo].[PLUG] AS M 
ON I.IPROD = M.BCHLD 
where BMWHS in ('22','93') AND BID=  'BM' and BDDIS >= 20200420

SELECT DISTINCT
I.IPROD,
I.IITYP,
IMSSPC

INTO THIRDTAB FROM SOCK AS I LEFT OUTER JOIN ADAPTER AS F ON I.IPROD = F.RPROD
WHERE (RTWHS in ('22','93') and RID = 'RT'and RDDDT>= '20200420')


SELECT DISTINCT ILPROD 
INTO TWOYEARDATA 
FROM RECTIFIER AS RECTIFIER INNER JOIN DIODE DIODE
ON RECTIFIER.SICOMP=ILCOMP
AND RECTIFIER.SIINVN=ILINVN
AND RECTIFIER.SIORD=ILORD
AND RECTIFIER.IHDPFX=ILDPFX
AND RECTIFIER.IHDYR=ILDYR
AND RECTIFIER.SICUST = DIODE.ILCUST
INNER JOIN SOCK I ON I.IPROD = DIODE.ILPROD

Where RECTIFIER.SICOMP = '93' 
AND RECTIFIER.IHDYR in ('18','19','20')
AND DIODE.ILLTYP = 'R'
AND I.IID = 'IM' -- 27052
AND I.IPFDV NOT IN ('AB','ME') -- 24821

SELECT DISTINCT
I.IPROD,
I.IITYP,
IMSSPC,
INTO FOURTHTAB
FROM SOCK AS I INNER JOIN TWOYEARDATA AS T ON I.IPROD= T.ILPROD



SELECT * INTO FINAL
FROM
(
SELECT * FROM FIRSTTAB UNION
SELECT * FROM SECTAB UNION
SELECT * FROM THIRDTAB UNION
SELECT * FROM FOURTHTAB
)R

END

what i am trying is to remove hard coding of values from where clause.

The below value i am trying to make dynamic.

**WHERE ICLAS in ('01','02','03','04','13','14') and IID='IM' AND IPFDV NOT IN ('AB','ME') 
where BMWHS in ('22','93') AND BID=  'BM' and BDDIS >= 20200420
WHERE (RTWHS in ('22','93') and RID = 'RT'and RDDDT>= '20200420')
Where RECTIFIER.SICOMP = '93' AND RECTIFIER.IHDYR in ('18','19','20') AND DIODE.ILLTYP = 'R' AND I.IID = 'IM' AND I.IPFDV NOT IN ('AB','ME')** 

Thanks a ton

Answer 1

CREATE TYPE string_list TABLE (str varchar(10) NOT NULL PRIMARY KEY
go
CREATE PROCEDURE usp_prdoduct 
   @iclas string_list READONLY,
   @no_ipdfc READONLY, 
   ...

 SELECT DISTINCT
 IPROD,
 IITYP,
 IMSSPC

 INTO FIRSTTAB
 FROM TEMPA WHERE ICLAS in (SELECT str FROM @iclas) and IID='IM' 
 AND IPFDV NOT IN (SELECT str FROM @no_ipfdv)

I don't know if you have any use for it, but on my web site I have an article for use table variables from .NET, http://www.sommarskog.se/arrays-in-sql-2008.html.

Answer 2

Hi @Uma ，

Using dynamic sql can pass parameters through variables, but if hard coding works, it is best to choose hard coding instead of dynamic sql.And in your code, you need to add a lot of variables to convert to dynamic sql.
If you have to use dynamic sql, just add variables according to the suggestions provided by Viorel-1.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html