Can you put a condition in an EXEC? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can you put a condition in an EXEC?

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-25 : 15:07:48

Like

 CREATE SPROC @Field, @Filter AS BEGIN
EXEC('SELECT ' + @Field + ' FROM Table' +
CASE WHEN @Filter = 1 THEN 'Where User = 1' END
END

So When Filter = 1 It will RUN SELECT [...] WHERE User = 1 to the same batch of code, but when it is, it will run the select statement without it?

Thanks,
Arithomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-25 : 15:10:43

I don't understand why you are using dynamic sql for this. Could you show exactly what you want to do?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-25 : 15:20:10

I have a column (Table1.Column1) That I'm using as a flag for:

IF Table2.Field1 AND Table2.Field2 are NULL, then 0
IF Table2.Field1 OR Table2.Field2 are NULL, then 1

However, Table2 is obtained through a join, and it is faster to run them separately then state them in the same ON clause, so instead:

IF Table2.Field1 IS NOT NULL, then 1
ELSE
IF Table2.Field2 IS NOT NULL, then 1
ELSE 0

---------
Ignorance may be bliss, but knowledge is thrill.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-25 : 16:14:03

[code]
SELECT * FROM Table a
where
coalesce(@Filter,a.User) = a.User
[/code]

If filter is null then no filtering, if it's =1 then user is = 1.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources