Stored procedure – multivalue parameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure – multivalue parameter

Author

Topic

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-16 : 16:10:41

I need to create a stored procedure where a user can input several values for a given parameter – the logic is as below. Is there a way of doing this without seperating the parameter values with a comma? E.g the user will execute the procedure as
EXECUTE dbo.GetClientDetails '1 3 4'
And get the same results...

CREATE PROC dbo.GetClientDetails
(
@ID varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @GetAll varchar(600)

SET @GetAll =
'SELECT *
FROM dbo.Client
WHERE ClientId IN (' + @ID + ')'
EXEC(@GetAll)
END
GO

EXECUTE dbo.GetClientDetails '1,3,4'

Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-03-16 : 17:19:00

when executing procedure one must declare input parameter(s). if you want to use solely one, take into consideration to have either a function | subprocedure to split input string and read all the relevant data and run it within procedure.

sqlnewbie10
Starting Member

8 Posts

Posted - 2010-03-16 : 17:24:50

quote:
when executing procedure one must declare input parameter(s). if you want to use solely one, take into consideration to have either a function | subprocedure to split input string and read all the relevant data and run it within procedure.

@slimt_slimt

Do you have an example of something like this

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-16 : 17:26:17

For quick solution .... Using replace function to replace ' ' to ','
Try to see if it works for you.

EXECUTE dbo.GetClientDetails '1 3 4'

CREATE PROC dbo.GetClientDetails
(
@ID varchar(500)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @GetAll varchar(600)
SET @ID = replace(@ID, ' ', ',')
SET @GetAll =
'SELECT *
FROM dbo.Client
WHERE ClientId IN (' + @ID + ')'
EXEC(@GetAll)
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 01:28:03

see

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources