Author |
Topic |
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 2009-04-17 : 17:37:06
|
How can I do something likeEXEC sp_test '1,2,3,4,5' My SP should look like this ?CREATE SP sp_test( @EmpIdList varchar(512) )ASSelect * 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
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-17 : 22:41:09
|
create proc sp_test(@EmpIdList varchar(512))as set nocount onbeginSelect * from Employees where '%,' + @EmpIdList + ',%' LIKE '%,' + CAST( id AS VARCHAR(255)) +',%' endset nocount offexec sp_test '1,2,3,4,5' |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-04-18 : 00:33:17
|
Google :Passing array in store procedure. |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-04-18 : 01:55:12
|
u can use through dynamic sql alsoexec(Select * from Employees where cast(empid as varchar) in('+@EmpIdList+')') |
|
|
Kokkula
Starting Member
41 Posts |
Posted - 2009-04-18 : 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)+',%' ENDHope helpful...Thanks,Pavan |
|
|
nathans
Aged Yak Warrior
938 Posts |
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 2009-04-20 : 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 haverow 1 : ids[0], startDates[0], endDates[0]row 2 : ids[1], startDates[1], endDates[1]etc .... |
|
|
GoDaddy
Yak Posting Veteran
64 Posts |
Posted - 2009-04-20 : 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
938 Posts |
Posted - 2009-04-20 : 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 - 2009-04-20 : 13:18:29
|
It's only the order .... unless there's another way ...? |
|
|
|