question

Uma-6714 avatar image
0 Votes"
Uma-6714 asked EchoLiu-msft commented

How to use table value parameter in Stored Procedure 2016

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

sql-server-generalsql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Did you consider an approach that does not require table valued parameters?

For example, for the first WHERE you can add the next parameters:

 ALTER PROC usp_product
     @ICLASS_to_include varchar(max),
     @IPFDV_to_exclude varchar(max),
     @IID varchar(10)
 AS
 . . .

Then try the next conditions:

 WHERE ICLAS in (select value from string_split(@ICLASS_to_include, ',' ))
 and IID = @IID 
 and IPFDV NOT IN (select value from string_split(@IPFDV_to_exclude, ',' ))

When you call the procedure, pass comma-separated values as in your hard-coded approach.

In contrast, table-valued parameters seem to require additional types created with CREATE TYPE but have certain advantages in some circumstances.



0 Votes 0 ·

@Viorel-1 : How to handle where clause from other Select statement of the code.

0 Votes 0 ·

Add more corresponding parameters and use a similar approach based on STRING_SPLIT.

1 Vote 1 ·

Do you have any updates?
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
 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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered Uma-6714 commented

Hi @Uma-6714

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://docs.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Echo, why would it have to be dynamic SQL? Uma wants to avoid the hardcoding, and wants the values to be dynamic, but that is not the same as dynamic SQL. As a matter of fact, Uma explicitly asked about table-valued parameters.

1 Vote 1 ·