Stored procedure with "array" input

Hi!

I have a Stored Procedure that returs users based on different input parameters. Please see attached code.

The stored procedure works just fine, but now I need to do a modification. As you see there is an input called @County. This input expects a tinyint, like "2". The problem is that the procedure only can return users from one spesific county, ex "2". I really would like the procedure to return users from multple counties!

Is it possible to make a modification so that @County allow multiple ID's, like "2,3" or "2,3,8" ?

ALTER PROCEDURE [dbo].[spSearchUsers] 
(
	@UserName varchar(256) = NULL,
	@County tinyint = NULL,
	@PageNumber int,
	@PageSize int,
	@SiteId smallint,
	@TotalUsers int OUTPUT
)
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @sql nvarchar(max)
	DECLARE @Lower int
	DECLARE @Upper int
	DECLARE @Total int

	SET @Lower = @PageSize * @PageNumber
	SET @Upper = @PageSize - 1 + @Lower

	-- CREATE TEMP TABLE
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0,1) NOT NULL,
		UserId uniqueidentifier, 
		CreateDate varchar(64)
	)

	-- FILL TEMP TABLE WITH USERID AND CREATEDATE (FOR SORTING)
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' UserId, CreateDate FROM vw_Users WHERE SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County > 0
	BEGIN
		SET @sql = @sql + ' AND County = ' + CAST(@County as varchar(2))
	END

	SET @sql = @sql + ' ) qt ORDER BY'	
	SET @sql = @sql + ' CreateDate DESC'

	INSERT INTO #PageIndexForUsers(UserId, CreateDate)
		EXEC sp_executesql @sql


	-- SELECT STATEMENT THAT TAKES OUT RECORDS BASED ON INDEX NUMBER AND PAGING IN TEMP TABLE 
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' u.UserId, u.UserName, u.FirstName, u.LastName FROM vw_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND p.IndexId >= ' + CAST(@Lower as varchar(4)) + ' AND p.IndexId <= ' + CAST(@Upper as varchar(4)) + ' AND SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County > 0
	BEGIN
		SET @sql = @sql + ' AND County = ' + CAST(@County as varchar(2))
	END

	SET @sql = @sql + ' ) qt ORDER BY'    
	SET @sql = @sql + ' CreateDate DESC'

	EXEC sp_executesql @sql

	--RETURN TOTAL NUMBER OF RECORDS
	SELECT @TotalUsers = COUNT(*) FROM #PageIndexForUsers
	
	DROP TABLE #PageIndexForUsers

END

Open in new window

LVL 1
webressursAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plusone3055Commented:
you'll have to change the input parameters first on your application/code
becuase this SP is returning based on the parameters your giving it

so its only looking for one number
you cannot change the SP to take multiple ID's unless there are multiple ID's being entered by the user and not in the same textbox.. truthfully you can do that but it will get messy and you'll have to write try catch blocks in case someone enters a comma by mistake.

Best strategy
create another input parameter for the application
then create a Country 2
then you can modidy the stored procedure with an OR to include both county ID's
0
sameer2010Commented:
Try this
ALTER PROCEDURE [dbo].[spSearchUsers] 
(
	@UserName varchar(256) = NULL,
	@County varchar(200) = NULL,
	@PageNumber int,
	@PageSize int,
	@SiteId smallint,
	@TotalUsers int OUTPUT
)
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @sql nvarchar(max)
	DECLARE @Lower int
	DECLARE @Upper int
	DECLARE @Total int

	SET @Lower = @PageSize * @PageNumber
	SET @Upper = @PageSize - 1 + @Lower

	-- CREATE TEMP TABLE
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0,1) NOT NULL,
		UserId uniqueidentifier, 
		CreateDate varchar(64)
	)

	-- FILL TEMP TABLE WITH USERID AND CREATEDATE (FOR SORTING)
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' UserId, CreateDate FROM vw_Users WHERE SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County is not null
	BEGIN
		SET @sql = @sql + ' AND County in (''' + @County + ''')'
	END

	SET @sql = @sql + ' ) qt ORDER BY'	
	SET @sql = @sql + ' CreateDate DESC'

	INSERT INTO #PageIndexForUsers(UserId, CreateDate)
		EXEC sp_executesql @sql


	-- SELECT STATEMENT THAT TAKES OUT RECORDS BASED ON INDEX NUMBER AND PAGING IN TEMP TABLE 
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' u.UserId, u.UserName, u.FirstName, u.LastName FROM vw_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND p.IndexId >= ' + CAST(@Lower as varchar(4)) + ' AND p.IndexId <= ' + CAST(@Upper as varchar(4)) + ' AND SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County is not null
	BEGIN
		SET @sql = @sql + ' AND County in ('' ' + @County + ''')'
	END

	SET @sql = @sql + ' ) qt ORDER BY'    
	SET @sql = @sql + ' CreateDate DESC'

	EXEC sp_executesql @sql

	--RETURN TOTAL NUMBER OF RECORDS
	SELECT @TotalUsers = COUNT(*) FROM #PageIndexForUsers
	
	DROP TABLE #PageIndexForUsers

END
                                  

Open in new window

0
Scott PletcherSenior DBACommented:
In SQL 2008, you could use a table parameter to pass in multiple values.

In SQL 2005, probably the best option you have is xml.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sameer2010Commented:
My mistake. You do not need quotes. Try this instead
ALTER PROCEDURE [dbo].[spSearchUsers] 
(
	@UserName varchar(256) = NULL,
	@County varchar(200) = NULL,
--comma separated list
	@PageNumber int,
	@PageSize int,
	@SiteId smallint,
	@TotalUsers int OUTPUT
)
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @sql nvarchar(max)
	DECLARE @Lower int
	DECLARE @Upper int
	DECLARE @Total int

	SET @Lower = @PageSize * @PageNumber
	SET @Upper = @PageSize - 1 + @Lower

	-- CREATE TEMP TABLE
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0,1) NOT NULL,
		UserId uniqueidentifier, 
		CreateDate varchar(64)
	)

	-- FILL TEMP TABLE WITH USERID AND CREATEDATE (FOR SORTING)
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' UserId, CreateDate FROM vw_Users WHERE SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County is not null
	BEGIN
		SET @sql = @sql + ' AND County in (' + @County + ')'
	END

	SET @sql = @sql + ' ) qt ORDER BY'	
	SET @sql = @sql + ' CreateDate DESC'

	INSERT INTO #PageIndexForUsers(UserId, CreateDate)
		EXEC sp_executesql @sql


	-- SELECT STATEMENT THAT TAKES OUT RECORDS BASED ON INDEX NUMBER AND PAGING IN TEMP TABLE 
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' u.UserId, u.UserName, u.FirstName, u.LastName FROM vw_Users u, #PageIndexForUsers p WHERE u.UserId = p.UserId AND p.IndexId >= ' + CAST(@Lower as varchar(4)) + ' AND p.IndexId <= ' + CAST(@Upper as varchar(4)) + ' AND SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	IF @County is not null
	BEGIN
		SET @sql = @sql + ' AND County in ( ' + @County + ')'
	END

	SET @sql = @sql + ' ) qt ORDER BY'    
	SET @sql = @sql + ' CreateDate DESC'

	EXEC sp_executesql @sql

	--RETURN TOTAL NUMBER OF RECORDS
	SELECT @TotalUsers = COUNT(*) FROM #PageIndexForUsers
	
	DROP TABLE #PageIndexForUsers

END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
winheimCommented:
Generate this new table valued function


Create FUNCTION [dbo].[fn_Split] (@text nvarchar(4000), @delimiter char(1) = ' ')
 
RETURNS @Strings TABLE
(
  position int IDENTITY PRIMARY KEY,
  value nvarchar(4000)
)

AS
BEGIN
 
DECLARE @index int
SET @index = -1

 WHILE (LEN(@text) > 0)
 BEGIN
	SET @index = CHARINDEX(@delimiter , @text)

 IF (@index = 0) AND (LEN(@text) > 0)
 BEGIN
	 INSERT INTO @Strings VALUES (@text)
 	BREAK
 END

 IF (@index > 1)
 BEGIN
	INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
	SET @text = RIGHT(@text, (LEN(@text) - @index))
 END
 ELSE
 	SET @text = RIGHT(@text, (LEN(@text) - @index))
 END

 RETURN
END

Open in new window


test it like this

DECLARE @Country varchar(255)
SET @Country = '1,2,3,4,5'

SELECT * FROM dbo.fn_Split(@Country,',') fn

Open in new window


and use it in your code - i hope that this works

ALTER PROCEDURE [dbo].[spSearchUsers] 
(
	@UserName varchar(256) = NULL,
	@County varchar(1000) = '', @Country = '1,2,3,4,5'  --@County tinyint = NULL,
	@PageNumber int,
	@PageSize int,
	@SiteId smallint,
	@TotalUsers int OUTPUT
)
AS
BEGIN	
	SET NOCOUNT ON

	DECLARE @sql nvarchar(max)
	DECLARE @Lower int
	DECLARE @Upper int
	DECLARE @Total int

	SET @Lower = @PageSize * @PageNumber
	SET @Upper = @PageSize - 1 + @Lower

	-- CREATE TEMP TABLE
	CREATE TABLE #PageIndexForUsers
	(
		IndexId int IDENTITY (0,1) NOT NULL,
		UserId uniqueidentifier, 
		CreateDate varchar(64)
	)

	-- FILL TEMP TABLE WITH USERID AND CREATEDATE (FOR SORTING)
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' UserId, CreateDate FROM vw_Users '
	
	IF @County <> ''
	BEGIN
		SET @sql = @sql + ' INNER JOIN fn_Split('+ @County + ','','') fn ON fn.value = country'				
	END
	
	SET @sql = @sql + ' WHERE SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	


	SET @sql = @sql + ' ) qt ORDER BY'	
	SET @sql = @sql + ' CreateDate DESC'

	INSERT INTO #PageIndexForUsers(UserId, CreateDate)
		EXEC sp_executesql @sql


	-- SELECT STATEMENT THAT TAKES OUT RECORDS BASED ON INDEX NUMBER AND PAGING IN TEMP TABLE 
	SET @sql = 'SELECT * FROM ( SELECT DISTINCT'	
	SET @sql = @sql + ' u.UserId, u.UserName, u.FirstName, u.LastName FROM vw_Users u '
	
	IF @County <> ''
	BEGIN
		SET @sql = @sql + ' INNER JOIN fn_Split('+ @County + ','','') fn ON fn.value = country'				
	END
		
	SET @sql = @sql + ' INNER JOIN #PageIndexForUsers p WHERE u.UserId = p.UserId AND p.IndexId >= ' + CAST(@Lower as varchar(4)) + ' AND p.IndexId <= ' + CAST(@Upper as varchar(4)) + ' AND SiteId = ' + CAST(@SiteId as varchar(2)) 

	IF @UserName IS NOT NULL
	BEGIN
		SET @sql = @sql + ' AND UserName LIKE ''%' + @UserName + '%'''
	END	

	SET @sql = @sql + ' ) qt ORDER BY'    
	SET @sql = @sql + ' CreateDate DESC'

	EXEC sp_executesql @sql

	--RETURN TOTAL NUMBER OF RECORDS
	SELECT @TotalUsers = COUNT(*) FROM #PageIndexForUsers
	
	DROP TABLE #PageIndexForUsers

END

Open in new window

0
webressursAuthor Commented:
Worked perfect, thanks :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.