Pass Group into variable then query NOT IN variable?

Greetings all.

I'm looking for a clever way to pass a group into a variable and then query the variable using NOT IN

For example

DECLARE @.myvariable varchar(100)

SET @.myvariable = (1, 2, 3, 4, 5)

SELECT * FROM myTable

WHERE myField NOT IN @.myvariable

I swear I've seen this and I'm sure it's simple but my brain is not calculating the algorithm.

Thanks for all help,

Adamus

This works, but does require the dreaded dynamic SQL:

if object_id('myTable') is not null drop table myTable

go

create table myTable (myField int)

go

insert myTable values (1)

insert myTable values (2)

insert myTable values (3)

insert myTable values (4)

insert myTable values (5)

insert myTable values (6)

insert myTable values (7)

go

DECLARE @.myvariable varchar(100)

SET @.myvariable = '(1, 2, 3, 4, 5)'

exec('SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable)

Ron

|||

try

Code Snippet

DECLARE @.myvariable varchar(100),

@.stmt varchar(8000)

SET @.myvariable = (1, 2, 3, 4, 5)

SET @.stmt = 'SELECT * FROM myTable WHERE myField NOT IN ' + @.myvariable

exec sp_sqlexec @.stmt

|||

That's it!

Thanks,

Adamus

|||

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

|||

Dale,

I suspect you meant:

EXECUTE sp_executesql @.Stmt

|||

Yup, thanks.

Leftover from days gone by. Also need to change varchar to nvarchar...

|||

Arnie Rowland wrote:

Rice31416,

Unfortunately, your example demonstrates the use of "the dreaded dynamic SQL." That what you are executing in the EXEC() function.

Perhaps you meant something else...

This was the answer I was looking for but what do you mean by "unfortunately?"

Is there something bad about using this approach?

Adamus

|||

Hi Adam,

Using Dynamic SQL is sometimes a necessary 'evil'. At least some folks consider its usage to be tandamont to 'evil'. (My comment about the "dreaded" was to reflect back to Rice31416 that while he wrote that his approach didn't use "the dreaded dynamic SQL", in fact, his approach was using dynamic SQL."

I recommend this article from Erland Sommarskog. It gets into advanced issues, but keep it for reference.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html

|||

Arnie,

Yes, I meant that it *does* involve dynamic SQL. And yes, I got lazy and used "execute" in the example instead of sp_executesql!

Ron