I want to create a store procedure like this
CREATE PROCEDURE test
     @.values   VARCHAR(8000)
AS
SELECT    *     
FROM      MyTable
WHERE    MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks  :p 
 Franky
franky@.boucheros.comTry writing the SELECT sentence an a string and then EXECUTE this string.
Originally posted by Franky 
I want to create a store procedure like this
CREATE PROCEDURE test
     @.values   VARCHAR(8000)
AS
SELECT    *     
FROM      MyTable
WHERE    MyTable.ID IN(@.values)
GO
the parameter @.values = '''1'',''3'',''5'',''6'',''7'''
How can I do that??
Thanks  :p 
 Franky
franky@.boucheros.com|||CREATE PROCEDURE test (@.values VARCHAR(8000))
as
Declare @.Query nVarchar(1000)
SET @.Query=N'SELECT * FROM Table ' +
            'WHERE Table.ID IN( ' + @.values + ')'
EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35
====================================
Where @.values must be
@.values = '''1''' + ','+ '''3'''+ ',' + '''5''' + ','+'''6'''+','+'''7'''|||Lots of ways to skin this cat.  A non-dynamic solution:
SELECT * 
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman|||Originally posted by blindman 
Lots of ways to skin this cat.  A non-dynamic solution:
SELECT * 
FROM MyTable
WHERE @.values like '''%' + cast(MyTable.ID as varchar(4)) + '%'''
Another method would be to create a user-defined funtion that returns a table of values from your string.
blindman  
Or this way
SELECT * 
FROM MyTable
WHERE CHARINDEX(@.values,cast(MyTable.ID as varchar))>0|||Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman|||Originally posted by blindman 
Snail, I think you will need to put quotes around your value so that a value such as 1 doesn't match up with a string like ("8", "9", "10", "11").
blindman  
blindman - it was my fault but another one - it needs to change order of arguments in charindex function. Nothing is wrong with quotes. Check this one:
create table #test(id int identity,code varchar(10))
insert #test(code) values('a')
insert #test(code) values('b')
insert #test(code) values('c')
declare @.list varchar(80)
set @.list='''1'',''3'',''5'',''6'',''7'''
select * from #test
where  CHARINDEX(cast(id as varchar),@.list)>0
 
No comments:
Post a Comment