Passing multiple values to SP
In this article,I explain about passing multiple values to a SP in order to retrieve the records from the table.
Here, I used one table valued function to split the parameters and I choose comma as a delimiter to split.
This is the sample table #temp1 with columns id,name and date1.
create table #temp1(id int,name varchar(15),date1 datetime)
insert into #temp1 values(1,'a',GETDATE())
insert into #temp1 values(1,'b',GETDATE())
insert into #temp1 values(2,'a',GETDATE())
insert into #temp1 values(3,'g',GETDATE())This is the Split Function to split the values and returns table @IDTable with column item containing the splitted values.
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_NAME = N'Split'
)
DROP FUNCTION Split
go
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
--SET @tempItemList = REPLACE (@tempItemList,' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
GOThis is the Stored Procedure which takes multiple values as parameter and these values are passed to the split function and retrieves the splitted values.By using these values, the query retrieves the records as per the user needs.
If object_id('sp_name') is not null
Drop proc sp_name
Go
Create Proc sp_name
@param varchar(100)
As
Begin
declare @strqry1 varchar(2000)
drop table p
select item into p from Split(@param,',')
Set @STRQRY1 =' select * FROM #temp1 where id in (SELECT Item FROM p)'
EXEC(@STRQRY1)
END
goFor Execution...
exec sp_name '1,2,3'Output:
1 a 2011-06-30 14:47:20.753
1 b 2011-06-30 14:47:24.503
2 a 2011-06-30 14:47:50.280
3 g 2011-06-30 14:58:35.700
Hi keerthi,
It is very nice...
Keep on post article like this.
All the best