| Author |
 Topic  |
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
 Posted - 04/17/2009 : 17:37:06
|
How can I do something like
EXEC sp_test '1,2,3,4,5'
My SP should look like this ?
CREATE SP sp_test
( @EmpIdList varchar(512) )
AS
Select * from Employees
WHERE id IN (@EmpIdList)
Is the only way is to have a function that will convert the list parameter into a table of Ids ??? is there a simpler way without to have a function? |
|
|
bklr
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 04/17/2009 : 22:41:09
|
create proc sp_test
(@EmpIdList varchar(512))
as
set nocount on
begin
Select * from Employees where '%,' + @EmpIdList + ',%' LIKE '%,' + CAST( id AS VARCHAR(255)) +',%'
end
set nocount off
exec sp_test '1,2,3,4,5'
|
 |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 04/18/2009 : 00:33:17
|
Google :
Passing array in store procedure. |
|
|
|
bklr
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 04/18/2009 : 01:55:12
|
u can use through dynamic sql also
exec(Select * from Employees where cast(empid as varchar) in('+@EmpIdList+')') |
|
|
|
Kokkula
Starting Member
India
41 Posts |
Posted - 04/18/2009 : 06:30:50
|
Hello,
Try this...
CREATE TABLE [dbo].[Test]
(
ID INT,
NAME NVARCHAR(50)
)
INSERT INTO [dbo].[Test] VALUES (1,'Sample1')
INSERT INTO [dbo].[Test] VALUES (2,'Sample2')
INSERT INTO [dbo].[Test] VALUES (3,'Sample3')
INSERT INTO [dbo].[Test] VALUES (4,'Sample4')
CREATE PROCEDURE [dbo].[Sample]
(
@ID NVARCHAR(50)
)
BEGIN
DECLARE @IDSTR NVARCHAR(100)
DECLARE @ID NVARCHAR(50)
SET @ID = '1,2'
SET @IDSTR = CHAR(39) + REPLACE(@ID,',',CHAR(39) + ',' + CHAR(39)) + CHAR(39)
PRINT @IDSTR
SELECT ID
,NAME
FROM [dbo].[Test]
WHERE ','+ @IDSTR + ',' LIKE '%,' + CHAR(39)+ CAST(ID AS NVARCHAR) + CHAR(39)+',%'
END
Hope helpful...
Thanks,
Pavan |
|
|
|
nathans
Aged Yak Warrior
USA
933 Posts |
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 04/20/2009 : 10:22:47
|
Cool I use the smith solution with a UDF that can convert a list of int, string, datetime into a table (int, string, datetime): function [CSVTable]
I have this SP with param that are a list : @Ids, @startDates, @endDates.
How can I create a table from those list to have
row 1 : ids[0], startDates[0], endDates[0]
row 2 : ids[1], startDates[1], endDates[1]
etc ....
|
|
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 04/20/2009 : 11:03:50
|
I've use cursors to resolve my issue stated above.
But I don't think it's the best solution. There must be a better solution .... |
|
|
|
nathans
Aged Yak Warrior
USA
933 Posts |
Posted - 04/20/2009 : 12:54:10
|
How is each element related? Is there some Id that we can use to join the Ids / StartDates / EndDates datasets? Or is it only the order that they occur in the csv array that links them?
Nathan Skerl |
|
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 04/20/2009 : 13:18:29
|
It's only the order .... unless there's another way ...?
|
|
|
| |
Topic |
|
List of parameters in SP
