EXISTS with EXEC

2 Dec 2005 11:28 AM

Evan Camilleri

This works:

IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @qci_pk)

But since I may need to build the Sql statement I tried something like this,
which did NOT work,

IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk = @qci_pk'))

Is there a way to go around this?

Evan

2 Dec 2005 7:34 PM

Anith Sen

>> Is there a way to go around this?

The EXISTS clause in SQL can have only a SELECT statement. Can you elaborate
on what your requirements are? Why do you want to do something like this?

Alternatively you can use the entire IF clause in your EXEC like:

EXEC ( 'IF NOT EXISTS ( SELECT ... ) ... ELSE .. ' )

--
Anith

3 Dec 2005 4:53 PM

Erland Sommarskog

Evan Camilleri (e7***@yahoo.co.uk.nospam) writes:

> This works:
>
> IF NOT EXISTS(SELECT qci_pk FROM tb_Qci WHERE qci_pk = @qci_pk)
>
> But since I may need to build the Sql statement I tried something like
> this, which did NOT work,
>
> IF NOT EXISTS(EXEC('SELECT qci_pk FROM tb_Qci WHERE qci_pk =
> @qci_pk'))
>
> Is there a way to go around this?

One way is to use sp_executesql:

SELECT @sql = 'SELECT @x = CASE WHEN EXISTS (SELECT ...) THEN 1 ELSE 0 END'
EXEC sp_executesql @sql, N'@x bit OUTPUT', @exists OUTPUT
IF @exists = 0
...

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx