• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 62
  • Last Modified:

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

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
danielolorenz
Asked:
danielolorenz
3 Solutions
 
Shaun KlineLead Software EngineerCommented:
0
 
danielolorenzAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
SharathData EngineerCommented:
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
 
danielolorenzAuthor Commented:
Excellent help.  Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now