CODEWITHSUNDEEP
sqlsql-server
Libin Jose
Libin Jose

Reputation: 37

Using CSV string Values in 'IN' clause of Dynamic SQL

I have a stored procedure which accepts csv string as the parameter , for eg. ('IN\libin.jose,IN\Pallabi.P'). Stored procedure contains some dynamic sql which make use of this parameter inside IN clause eg : (AND u1.UserName IN (' + @UserNames + ')). Since this is being inside dynamic sql ,The condition is not passing without appending extra single quotes to the CSV values eg ('''IN\libin.jose '' ,''IN\Pallabi.P''')

--exec [GetUwParameterDetails] 'IN\libin.jose,IN\Pallabi.P' , 'false'
ALTER PROCEDURE [dbo].[GetUwParameterDetails]

@UserNames nvarchar(max),
@IncludeInactiveusers bit

AS
BEGIN

declare @selectedUsers nvarchar(max)
--set @selectedUsers = '''IN\libin.jose ''	,''IN\Pallabi.P''';
set @selectedUsers = 'IN\libin.jose,IN\Pallabi.P';
declare @selectedPermissions nvarchar(max)
set @selectedPermissions = '''Underwrite'',''ManageUwTeamPipeline''';

DECLARE @parameterQuery1 AS NVARCHAR(MAX);



set @parameterQuery1 = '

;WITH cte_users
AS (
	SELECT users.id
		,users.UserName
		,users.FirstName
		,users.lastname
		,users.Email
		,users.E3UserName	
		,UserStatus.[Status]
		,Widgets.[Description] DefaultWidget
	FROM users 
	INNER JOIN userparametervalues upv ON users.id = upv.userid
	INNER JOIN Parameters p on upv.ParameterId = p.id 
		AND p.Name = ''UwHierarchy''
	INNER JOIN UserPermissions up ON users.id = up.userid
	INNER JOIN [Permissions] ps on up.PermissionId = ps.Id 
		AND ps.IsActive = 1 AND ps.Name IN ('+ @selectedPermissions +')
	INNER JOIN users AS u1 ON upv.value = u1.id
		AND u1.UserName IN (' + @UserNames + ')	
    INNER JOIN UserStatus ON users.StatusId = UserStatus.Id
	LEFT JOIN Widgets ON users.WidgetId = Widgets.Id )select * from cte_users ';
	exec @parameterQuery1

	END

How can I achieve this ?

Upvotes: 1

Views: 334

Answers (1)

Madhivanan
Madhivanan

Reputation: 13700

Use this after BEGIN

set @UserNames =''''+replace(@usernames,',',''',''')+''''

Upvotes: 0

Related Questions