Solved

How Do You Pass In Multiselect Parameters Into a Stored Procedure?

Posted on 2016-08-05
5
37 Views
Last Modified: 2016-08-08
How Do You Pass In Multi-select parameters into a Stored Procedure?  

I need the results to be in a @LOB_KEY

the code:
		HAVING 	CLAIM_LAST_STATUS IN (@CLAIM_STATUS) 
			AND LOB_KEY IN (@LOB_KEY) 
			AND POLICY_YR IN (@POLICY_YR)

Open in new window


The @LOB_KEY needs to be passed in and partitioned into multiple values:
HAVING IN (value1, value2 etc..) CLAUSE

Any advice on a solution is appreciated.

Dan
0
Comment
Question by:danielolorenz
5 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 125 total points
ID: 41744766
0
 

Author Comment

by:danielolorenz
ID: 41744814
Here is my code:

declare @names varchar(200);

set @names = 'Dan, Dirk';

SELECT TOP 1000 [USER_KEY]
      ,[SECURITY_ID]
      ,[USER_FIRST_NM]
      ,[USER_LAST_NM]
      ,[USER_EMAIL]
      ,[ACTIVE_IND]
  FROM [BRAC_Portal].[dbo].[PORTAL_USER]
  where [USER_FIRST_NM] IN ('' + @names + '')

Open in new window


How do you get this code to work?  What is the best syntax?

Dan
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 125 total points
ID: 41744848
Create a user-defined table type, and pass the values into the proc as a table.  That is the modern approach to this issue.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 250 total points
ID: 41744984
try this.
declare @names varchar(200);

set @names = 'Dan, Dirk';

SELECT TOP 1000 [USER_KEY]
      ,[SECURITY_ID]
      ,[USER_FIRST_NM]
      ,[USER_LAST_NM]
      ,[USER_EMAIL]
      ,[ACTIVE_IND]
  FROM [BRAC_Portal].[dbo].[PORTAL_USER]
  where [USER_FIRST_NM] IN (
  SELECT ltrim(SUBSTRING(names, n, CHARINDEX(',', names + ',',n) - n))
 FROM (SELECT @names names) t1
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + names, n, 1) = ','
  AND n < LEN(names) + 1)

Open in new window

0
 

Author Closing Comment

by:danielolorenz
ID: 41747712
Excellent help.  Thanks.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
separate column 24 21
Sql query for filter 12 24
SQL Split character from numbers 3 18
How toselect unique values 3 10
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now