if not exists (exec(@sql) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if not exists (exec(@sql)

Author

Topic

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-29 : 16:23:25

I have a dynamically built sql statement.

I need to know when executed if it returns any results. if not I need to execute another sql statement.

trying

IF NOT EXISTS ( EXEC(@SQLStatement) )
Begin
EXEC(@SQLStatement)

i get an error on the exec -- how do i get around this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 16:30:17

insert into a temp table and check if that is empty?
Put the logic inside the dynamic sql so you only execute one statment?

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-29 : 16:32:43

im trying to execute one query. if i get no results then I want to execute another -- how would i put that logic into a sql statement?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 16:33:50

EXEC (@SQLStatement)
If @@RowCount = 0
EXEC(@SomeOtherSQLStatement)

Depends what that dynamic SQL is doing whether that will work or not.

--
Gail Shaw
SQL Server MVP

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 16:38:13

Add it to your dynamic sql
select @sql = '
if exists (select * from tbl)
select * from tbl
else
select * from tbl2'
exec (@sql)

An issue with Gail's suggestion might be that the first exec will return an empty resultset if no rows - that might not be a problem though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-29 : 17:03:51

im trying to speed it up so i dont think putting it in the logic and running the query twice is a good option

as for the

EXEC (@SQLStatement)
If @@RowCount = 0
EXEC(@SomeOtherSQLStatement)

EXEC (@SQLStatement)
If @@RowCount = 0
Print 'here'
else
Print 'here1'

doest print

tpiazza55
Posting Yak Master

162 Posts

Posted - 2010-10-29 : 17:23:19

putting the logic in the dynamic sql gives me the desired result

but doesnt this run the query twice:

if exists (select * from tbl)
select * from tbl

is there a way around this?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 18:29:07

select @sql = 'select * into #a from @tbl
if exists (select * from #a)
select * from #a
else
select * from tbl2'
exec (@sql)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-10-29 : 18:42:06

New topic for this bit of the question at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=152331

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources