CREATE FUNCTION dbo.Split
(
@RowData nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Data nvarchar(100)
)
AS
BEGIN
Declare @Cnt int
Set @Cnt = 1
While (Charindex(@SplitOn,@RowData)>0)
Begin
Insert Into @RtnValue (data)
Select
Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
Set @Cnt = @Cnt + 1
End
Insert Into @RtnValue (data)
Select Data = ltrim(rtrim(@RowData))
Return
END
ref URL
create PROCEDURE [dbo].[SP]
(
@OrderList varchar(500)
)
AS
Begin
select * from table where id in
(select Data from dbo.Split
(@OrderList,','))
Hi,
You do not require split. See below example.
Change Order list to you have in your DB. Change table and coloumn name appropriately.
Test in sepeate query windows. Note: You pass list in comma delimited.
declare @Orderlist nvarchar(max)
set @Orderlist = '953,954,956,955'
Declare @myQuery nvarchar(max)
Set @myQuery= 'select *from Order where OrderID in ('+ @Orderlist +')'
exec (@myQuery)
anilr499
Member
94 Points
389 Posts
how to pass list of items as parameter to storedprocedure
Dec 19, 2012 05:23 AM|LINK
Hi
i have a stored procedure
create PROCEDURE [dbo].[SP]
(
@OrderList varchar(500)
)
AS
Begin
select * from table where id in ('+ @OrderList +')
here am passing orderlist....
when i execute like this
am not getting data
but when i hardcode in sp like this ...
then am getting daataaa...
Thankyou
alankarp
Contributor
2042 Points
345 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 05:31 AM|LINK
Hi.
You can do it by using split function in sql
function code :
you SP will be as follows
ref URL
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648
http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str
Profile
chaaraan
Contributor
2170 Points
484 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 06:05 AM|LINK
Hi
In this case you can try dynamic query like this
create PROCEDURE [dbo].[SP] (
@OrderList varchar(500)
) AS
Begin
DECLARE @SQL varchar(2000)
SET @SQL = 'select * from table where id in ('+ @OrderList +')'
EXEC(@SQL)
END
Regards,
charan
anilr499
Member
94 Points
389 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 07:16 AM|LINK
Hi
thank you for your support..
but when i do like this
create PROCEDURE [dbo].[SP]
(
@OrderList varchar(500)
)
AS
Begin
am getting error
Procedure SP, Line 22
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
vendan
Participant
856 Points
293 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 07:51 AM|LINK
Hi,
Try below simplest way no need function
select * from table where (@OrderList) LIKE '%'+id+'%'
Kalaivendan
Please Mark as Answer if this post helps you!
alankarp
Contributor
2042 Points
345 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 08:01 AM|LINK
hi,
need to specify the the coumn name in sub query i.e data. Please use below query
Profile
alankarp
Contributor
2042 Points
345 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 08:04 AM|LINK
Please use below query
Profile
pratiksolank...
Member
271 Points
76 Posts
Re: how to pass list of items as parameter to storedprocedure
Dec 19, 2012 11:31 PM|LINK
declare @Orderlist nvarchar(max) set @Orderlist = '953,954,956,955' Declare @myQuery nvarchar(max) Set @myQuery= 'select *from Order where OrderID in ('+ @Orderlist +')' exec (@myQuery)