Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Code Snippets » SQL »
Passing Multiple Values to Stored Procedure Parameter
|
Hi,
Just A Small Example
copy the code and run in your query analyzer
Creating a sample table called test
create table test(id int primary key identity(1,1),
name varchar(5))
Inserting data into the test table
insert into test values ('a')
insert into test values ('b')
insert into test values ('c')
insert into test values ('d')
insert into test values ('e')
insert into test values ('f')
insert into test values ('g')
insert into test values ('h')
insert into test values ('i')
check all the rows effected in to the test table by running the below query
select * from test
Now Create a Sample procedure for the test table
Create Procedure usp_Sample
@Id Int
As
Begin
Select Id,Name From Test Where Id=@Id
End
If we execute the above stored procedure it will return the row that has passed by the user
for example if i pass '1' then it will return row of id containing '1'
Exec usp_sample 1
Suppose if user want to select 1,2,3,4,5... then in query analyzer you can write query as
Select id,name from test where id in (1,2,3,4,5)
if you want to execute the above query through stored procedure you can write query as same as above and even you don't know how many id's that user will select
if you know he selects only 5 then you can create 5 parameters in stored procedure but at the time or dynamic id selection you may think that we create a varchar parameter and pass all the values
but if u do like Internally the stored procedure executes your query like
Select id,name from test where id in ('1,2,3,4,5')
which you will get empty o/p
to over come that situations create a stored procedure as in below format
Create Procedure usp_MultipleSample
@Id Varchar(Max)
As
BEGIN
IF OBJECT_ID('tempdb..#tmpTbl', 'U') IS NOT NULL
BEGIN
DROP TABLE #tmpTbl
END
SET @Id=REPLACE(@Id,' ','')
CREATE TABLE #tmpTbl (Id Varchar(5))
Declare @TempId Varchar(5)
If CharIndex(',',@Id)=0
Begin
Insert Into #tmpTbl (Id) Select @Id
End
While CharIndex(',',@Id)>0
Begin
Set @TempId=LEFT(@ID,CHARINDEX(',',@ID)-1)
SET @Id=RIGHT(@Id,LEN(@Id)-CHARINDEX(',',@Id))
If @Id <>'' And CharIndex(',',@Id)=0
Begin
Set @Id= @Id + ','
End
Insert Into #tmpTbl (Id) Select @TempId
End
Select t.Id,Name From test t Join #tmpTbl temp on t.Id=temp.Id
Drop Table #tmpTbl
END
Now to execute the above stored procedure by below query
Exec usp_MultipleSample '1,2,3'
Exec usp_MultipleSample '1'
hope you got idea regarding sending multipule values to a single parameter for stored procedure
hope it will help some one
|
Responses
|
| Author: kaka kaki 15 Sep 2009 | Member Level: Bronze Points : 1 | Instead of physically creating a table and dropping it, just create a table variable:
DECLARE @tmpTbl TABLE
(
Id Varchar(5)
)
instead of CREATE TABLE #tmpTbl (Id Varchar(5))
This way if you dont have rights to create a physical table in the database, your stored proc will still work.
| | Author: Praveen 16 Sep 2009 | Member Level: Diamond Points : 2 | hi,
Thanks for your Comment
but according to performance issue
Creating Temp Table is little bit faster that creating temp table variable
Regards,
Praveen
|
|