This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.
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:
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
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)
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
Asked:
-
2
- +1
3 Solutions
Here is my code:
How do you get this code to work? What is the best syntax?
Dan
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 + '')
How do you get this code to work? What is the best syntax?
Dan
Create a user-defined table type, and pass the values into the proc as a table. That is the modern approach to this issue.
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)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
https://www.experts-exchange.com/articles/1536/Delimited-list-as-parameter-what-are-the-options.html