Converting delimited varchar @parameter for use in NOT IN()

I am creating a stored procedure which is passed a comma delimited string of
ids as a varchar datatype. The param is to be used in an SQL statement such
as:
CREATE PROCEDURE GetFromTable
@.IDs varchar(255)
AS
SELECT * FROM table WHERE iId NOT IN(@.IDs)
GO
The problem is that the iId field is of datatype int, so i get an error
converting the varchar datatype @.IDs to int.
I can not use dynamic SQL as i am not able to give table level access. It
has to be via EXEC rights on the stored procedure.
Any Help?
Thanks
PatrickArrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
AMB
"Patrick Russell" wrote:

> I am creating a stored procedure which is passed a comma delimited string
of
> ids as a varchar datatype. The param is to be used in an SQL statement suc
h
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>
>|||You cannot do this "this way". You'd need to parse your string,
load the values into a TABLE variable and then reference your
table variable:
SELECT * FROM table WHERE iId NOT IN (select myid from @.MyTableVariable)
These two articles will help:
http://www.eggheadcafe.com/articles/20001002.asp
http://www.eggheadcafe.com/PrintSea...asp?LINKID=529
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.learncsharp.net/home/listings.aspx
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Patrick,
Parse the @.IDs into rows of a temp table or table variable then use a join
or a subselect. The in operator will not take a variable like this without
building dynamic SQL.
"Patrick Russell" <prussel@.cfl.rr.com> wrote in message
news:dB2Xd.105360$pc5.97052@.tornado.tampabay.rr.com...
>I am creating a stored procedure which is passed a comma delimited string
>of
> ids as a varchar datatype. The param is to be used in an SQL statement
> such
> as:
> CREATE PROCEDURE GetFromTable
> @.IDs varchar(255)
> AS
> SELECT * FROM table WHERE iId NOT IN(@.IDs)
> GO
> The problem is that the iId field is of datatype int, so i get an error
> converting the varchar datatype @.IDs to int.
> I can not use dynamic SQL as i am not able to give table level access. It
> has to be via EXEC rights on the stored procedure.
> Any Help?
> Thanks
> Patrick
>|||Hi Patrick.
You could write a function like...
-- pseudo code
create function udtSplitIDs( @.ids varchar(1000) )
returns @.IDsTable table
(
id int
)
as
begin
while (get position of comma)
begin
insert @.IDsTable values( @.strValue )
find next comma
end
return @.IDsTable
end
your select could then be:
SELECT * FROM table
WHERE iId NOT IN(SELECT * FROM udtSplitIDs(@.IDs))
Bryce|||Out of curiousity. A query like that should be avoided if possible in a
high-performance situation due to performance issues, I assume?