Multiple value in single 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

Multiple value in single parameter

Author

Topic

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-12 : 23:02:03

Hello,

I know I have posted the topic about multiple values in single parameter and I got this link (http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm) from one of my posts. However, I am looking for a simple way to do multiple values in one single parameter in my simple Stored Procedures. Let's say for example I have a column called RoomNumber and the value data type is INT.

Here is my Stored Procedures:

CREATE PROC ROOMVACANCY
@RoomNumber int,

SELECT vacancy, roomnumber
FROM hoteldb
WHERE Vacancy IN (@RoomNumber)

END

The value for roomnumber has 100 records.

I want to be able to select for more than selection when I execute this stored procedures. How do I do that in the simple way?

Thank you guys for helping

SQLBoy

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 00:38:00

DECLARE @Id VARCHAR(100) = '6,25,187'

SELECT * FROM Table
WHERE PATINDEX('%,' + CAST(Id AS VARCHAR(20))+',%', ',' + @Id+ ',') > 0

Veera

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-13 : 00:55:37

[code]
CREATE PROC ROOMVACANCY
@RoomNumber ~~int~~ varchar(max)
AS
BEGIN
SELECT vacancy, roomnumber
FROM hoteldb
WHERE ~~Vacancy~~ roomnumber IN (SELECT numberval FROM dbo.CSVTable(@RoomNumber ))

END
[/code]

this is how you execute the procedure
[code]execute ROOMVACANCY @RoomNumber = '1,234,567'[/code]

KH
[spoiler]Time is always against us[/spoiler]

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-13 : 01:32:08

Hi Khtan,

I got the error when I apply the code you wrote. This is the error message:
Msg 208, Level 16, State 1, Procedure MultiVal, Line 11
Invalid object name 'dbo.CSVTable'.

(1 row(s) affected)

SQLBoy

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-13 : 01:36:53

Hi Vera,

So, do we have to set the value in the declaration? is the set value can be any or has to be '6,25,187'?

DECLARE @Id VARCHAR(100) = '6,25,187'

SQLBoy

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 01:41:42

CREATE PROC ProcName
( @Number VARCHAR(MAX))
AS
SET NOCOUNT ON
BEGIN

DECLARE @SQL VARCHAR(MAX)
SET @SQL = '
SELECT * FROM YourTableName
WHERE Number IN ('+@Number+')'

EXEC (@SQL)
END

SET NOCOUNT OFF

EXEC ProcName '1,2,3'

Veera

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-13 : 01:56:19

Hi Vera, if you are using the Temp Table, how you apply your coding? For example, if the stores proc has one temptbl and has one join to that temp table?

SQLBoy

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-03-13 : 02:21:12

This Is the procedure , in this proc i can use parsevalues function. this function
use to separate the comma separate values to rows , so i can insert the comma separate values
into temp table and join the main table ..

USE DBTraining1
GO
ALTER PROC ProcName
( @Number VARCHAR(MAX))
AS
SET NOCOUNT ON
BEGIN

DECLARE @Temp TABLE(EmployeeID INT)
INSERT @Temp
SELECT * FROM ParseValues(@Number,',')

--DECLARE @SQL VARCHAR(MAX)
--SET @SQL = '
--SELECT * FROM OAS_Employee_info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty
--WHERE EmployeeId IN ('+@Number+')'
SELECT * FROM OAS_Employee_info.dbo.tbl_EmpInfo_EmpolyeeJoinInDuty AS EJD
INNER JOIN @Temp AS T
ON EJD.EmployeeId = T.EmployeeID
--EXEC (@SQL)
END

Function :

USE DBTraining1
GO
CREATE FUNCTION ParseValues
(@String varchar(8000), @Delimiter varchar(10) )
RETURNS @RESULTS TABLE (--ID int identity(1,1),
Val varchar(50))
AS
BEGIN
DECLARE @Value varchar(100)
WHILE @String is not null
BEGIN
SELECT @Value=
CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0
THEN LEFT(@String,PATINDEX('%'+@Delimiter+'%',@String)-1) ELSE @String END,
@String=
CASE WHEN PATINDEX('%'+@Delimiter+'%',@String) >0
THEN SUBSTRING(@String,PATINDEX('%'+@Delimiter+'%',@String)+LEN(@Delimiter),LEN(@String))ELSE NULL END
INSERT INTO @RESULTS (Val)
SELECT @Value
END
RETURN
END

Veera

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-03-13 : 02:44:04

quote:
Originally posted by SQLBoy14

Hi Khtan,

I got the error when I apply the code you wrote. This is the error message:
Msg 208, Level 16, State 1, Procedure MultiVal, Line 11
Invalid object name 'dbo.CSVTable'.

(1 row(s) affected)

SQLBoy

click on the link and get the function CSVTable() from there
here it is http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable

KH
[spoiler]Time is always against us[/spoiler]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-03-13 : 18:53:34

SQL Server (at least newer versions 2008+) has a really nice way to do this sort of thing called a Tabled-Valued Parameter. That is the suggested way you do these types of things. No need to use antiquated hacks to pass sets of data around.

Here are some links on how to implement a TVP:
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/
http://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx
http://technet.microsoft.com/en-us/library/bb510489.aspx

SQLBoy14
Yak Posting Veteran

70 Posts

Posted - 2014-03-14 : 00:23:27

Thank you Veera, Khtan and Lamprey for good info.

SQLBoy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources