Using VarChar variable with NOT IN 1

[RobHVB6Sql]

I am writing a stored procedure for a report.
The user is shown a list of all clients in a grid on a form. They then remove the database clients they do not want.

The procedure selects the basic list of clients, then I wish to remove some clients from the working table.
Ie

SELECT PersonID, aa, bb, cc from tblWhatever
WHERE PersonID NOT In (@VarChar)

The varchar is sent like this "231, 240, 250".

Do I need to loop though the working table to delete each personID in turn?
Or can I use @VarChar in the select statement?

Some code examples would be great, thanks in advance.

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)

 

[SonOfEmidec1100]

Use a technique called 'dynamic sql', this has many uses - search the forums for more info. Here is an example to get you started

declare @cmd varchar(200)
set @cmd = 'SELECT * from acc WHERE accid NOT In (' + @VarChar + ')'
exec(@cmd)

 

[RobHVB6Sql]

Awesome, thank you.
I can see myself using this many times.

Ps For others, I also found this thread useful -

http://www.tek-tips.com/viewthread.cfm?qid=914432

Rob Hasard
Data Manager -Genetic Services
(VB6 /SQL 7.0)