Problem with IN

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

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***
Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider?
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.developersdex.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:
>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

Problem with distributed transacion.

Hi everybody, I'm having problems with distributed transactions. Every
time i execute the store procedure, get this error:
System.Data.SqlClient.SqlException: The operation could not be performed
because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
transaction.
This process use to work, but on the weekend I change one of the server
are involved. The server that runs this query now has win2003 sp1,
sql2000 sp3 installed,
when it works has this configuration: win2000 sp4, sql2000 sp3.
The remote server has win2000 sp4, sql2000 sp3.
I will appreciate a lot your help.
*** Sent via Developersdex http://www.codecomments.com ***Network access to the distributed transaction coordinator
(MS DTC) is disabled by default on Windows 2003. Check the
following article for steps to enable it on the Windows 2003
box:
How to enable network DTC access in Windows Server 2003
http://support.microsoft.com/?id=817064
-Sue
On Tue, 17 Jan 2006 15:02:00 -0800, MariaGuzman
<marisa@.devdex.com> wrote:

>Hi everybody, I'm having problems with distributed transactions. Every
>time i execute the store procedure, get this error:
>System.Data.SqlClient.SqlException: The operation could not be performed
>because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed
>transaction.
>This process use to work, but on the weekend I change one of the server
>are involved. The server that runs this query now has win2003 sp1,
>sql2000 sp3 installed,
>when it works has this configuration: win2000 sp4, sql2000 sp3.
>The remote server has win2000 sp4, sql2000 sp3.
>I will appreciate a lot your help.
>
>
>
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help.
I apply the changes that you recommended to me, but this works only if i
use the begin transaction clause. But If I use the begin distributed
transaction, it doesnt work.
Do you know any other issue that i have to consider'
Thanks a lot!!
*** Sent via Developersdex http://www.codecomments.com ***|||Thanks a lot for your help. At the end, I resolve this problem following
this link.
http://support.microsoft.com/kb/831425/en-us
Regards,
*** Sent via Developersdex http://www.codecomments.com ***