Conditional SQL based on result of the first.

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

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional SQL based on result of the first.

Author

Topic

pcpham
Starting Member

10 Posts

Posted - 2009-08-13 : 00:33:09

Hi,

I have an SP that dynamically builds an SQL SELECT statement into a varchar based on the parameters passed through. The query is executed using EXEC(@qry) Now if there are results from this query then return to the caller otherwise perform a different select query using the same method.

So far I have...

SET @str = 'SELECT * from tblData WHERE SomeData = True'

EXEC(@str)

IF @@ROWCOUNT = 0
BEGIN
SET @str = 'SELECT * from tblAnother'
EXEC(@str)
END

----

Problem is if @@ROWCOUNT =0, It doesn't actually return any data.

Please help..

Thanks in advanced.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 00:42:42

@@rowcount will return the when any rows statsified the SELECT * from tblData WHERE SomeData = True

or try like this
declare @cnt int

select @cnt = COUNT(*) from tblData WHERE SomeData = True

SELECT * from tblAnother where @cnt <> 0

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-13 : 00:43:40

SET @str = 'SELECT * from tblData WHERE SomeData = True', i think this statement returns data.
@@rowcount means Returns the number of rows affected by the last statement.
if the execution statement contains data then it retuns value.

pcpham
Starting Member

10 Posts

Posted - 2009-08-13 : 01:49:24

Not sure I understand...Let me rephrase.

The first SQL statement 'SELECT * from tblData WHERE SomeData = True'. Now if there is data I want it to return the data and exit the storeprocedure with this resultset.

BUT

If there is no data, build a completely different query and return THAT resultset.

Problem is the EXEC does not fire especially if there is 'no data'.

Thanks again...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-13 : 02:23:45

SET @str = 'SELECT * from tblData WHERE SomeData = True'

EXEC(@str)

if(select @@rowcount) = 0
BEGIN
SET @str = 'SELECT * from tblAnother'
EXEC(@str)
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources