extracting csv string and using in NOTIN clause

Hi,
I'm trying to pass a stored proc a list of values in a comma seperated strin
g:
("3125,3126,3129...") The values represent an integer column in a table.
Then I want to use the string to exclude the values from a SELECT as in:
CREATE PROCEDURE myProc
@.transactionIDs varchar(255)
AS
SELECT TransactionID FROM SalesTransactions
WHERE TransactionID NOT IN (@.transactionIDs)
GO
SQL Server complains that it can't cast a varchar to an int
Any suggestions on how to do this?
Can someone provide an example?
ThanksWhile someone might suggest you use dynamic SQL for this, I
wouldn't. Instead, consider either this not-so efficient solution:
select TransactionID
from SalesTransactions
where
charindex(
',' + rtrim(TransactionID) + ',',
',' + @.transactionIDs + ','
) = 0
-- be sure @.transactionIDs has no spaces
or, for greater efficiency, the more involved solution
that uses a table-valued function to convert @.transactionIDs
into a table of id values:
http://groups.google.co.uk/groups?q...wierzbicki+kass
You can then write:
select TransactionID
from SalesTransactions
where TransactionID not in (
select Item from ListTable(@.transactionIDs)
)
Steve Kass
Drew University
Opa wrote:

>Hi,
>I'm trying to pass a stored proc a list of values in a comma seperated stri
ng:
>("3125,3126,3129...") The values represent an integer column in a table.
>Then I want to use the string to exclude the values from a SELECT as in:
>CREATE PROCEDURE myProc
> @.transactionIDs varchar(255)
>AS
>SELECT TransactionID FROM SalesTransactions
>WHERE TransactionID NOT IN (@.transactionIDs)
>GO
>SQL Server complains that it can't cast a varchar to an int
>
>Any suggestions on how to do this?
>Can someone provide an example?
>Thanks
>
>
>