Using a variable for the whole WHERE clause

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)

Using a variable for the whole WHERE clause

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-08-03 : 11:38:23

I want to DECLARE a variable and then use it in many SELECT statements for the WHERE clause e.g.

DECLARE @where varchar(50) SET @where = ' LASTNAME = ''Jones'''

This seems OK when tested without the select statement e.g.
DECLARE @where varchar(50) SET @where = ' LASTNAME = ''Jones'''
SELECT @where
returns LASTNAME = 'Jones'

However
DECLARE @where varchar(50) SET @where = ' LASTNAME = ''Jones'''
SELECT * FROM CUSTOMERS WHERE + @where
returns an error

Msg 4145, Level 15, State 1, Line 3
An expression of non-boolean type specified in a context where a condition is expected, near '@where'.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 11:41:31

One way is to use dynamic sql

DECLARE @where varchar(50) 
SET @where = ' LASTNAME = ''Jones'''
declare @sql varchar(1000)
set @sql = 'SELECT * FROM CUSTOMERS WHERE' + @where
exec(@sql)

Please read about the disadvantages of using dynamic sql..before you opt for this solution.

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-08-03 : 11:44:12

Thank you - for various, admittedly small, reasons I would really like to achieve this without Dynamic SQL - is that possible?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-03 : 11:51:20

[code]
SELECT * FROM CUSTOMERS c WHERE LASTNAME = COAELSCE(@LastName, c.LASTNAME )
[/code]

is another option

If you don't have the passion to help people, you have no passion

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-03 : 12:56:09

Maybe this link will help?
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Kristen
Test

22859 Posts

Posted - 2010-08-03 : 13:02:56

SELECT * FROM CUSTOMERS c WHERE LASTNAME = ~~COAELSCE~~COALESCE(@LastName, c.LASTNAME )

does not match rows where LASTNAME column has NULL values and @LastName parameter is NULL

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-03 : 13:33:25

From what I understand, OP wants the column in the WHERE clause also to be dynamic, not just the value.

SET @where = ' LASTNAME = ''Jones'''

Is there another way to do this without using dynamic sql?

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-08-03 : 13:33:35

Buzzard724 - You can't do that in any Language, unless it is some type of scripting. If you want to build a statement from strings, that is Dynamic SQL.

Are you familiar with VB?, just like you can't do this:


dim teststr = ".ExecuteNonQuery()"
cmd & teststr

And expect it to run as if it was a statement like

cmd.ExecuteNonQuery()

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-08-03 : 13:54:09

Thank you all - I am giving in quietly and using Dynamic SQL (with caveats understood about security).
Many useful points about how and why - thank you

see this link for best practice in creating Dynamic SQL
http://www.sommarskog.se/dyn-search-2005.html

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-08-03 : 13:55:39

very true! so Buzzard do you have rows where LASTNAME column has NULL values and @LastName parameter is NULL or is that required parm you are sending in.

quote:
Originally posted by Kristen

SELECT * FROM CUSTOMERS c WHERE LASTNAME = ~~COAELSCE~~COALESCE(@LastName, c.LASTNAME )

does not match rows where LASTNAME column has NULL values and @LastName parameter is NULL

If you don't have the passion to help people, you have no passion

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-08-03 : 14:27:24

quote:
Originally posted by Buzzard724

Thank you all - I am giving in quietly and using Dynamic SQL (with caveats understood about security).
Many useful points about how and why - thank you

see this link for best practice in creating Dynamic SQL
http://www.sommarskog.se/dyn-search-2005.html

Here are 2 other ideas - which may or may not be applicable:

1. Use Stored Procedure parameters.

Create SP_Test(@LastName, @FirstName ...

select * from Customer
where
(LastName = @LastName or @LastName is null) and
(FirstName = @FirstName or @FirstName is null) and
...

2. Build the SQL string from within the Application, then execute it directly from the application.

Kristen
Test

22859 Posts

Posted - 2010-08-04 : 02:29:43

2a. Build the SQL string within a Stored Procedure, then execute it directly using sp_ExecuteSQL

in case it is easier to do it SQL-server-side, rather than application-side. Might also help with table-permissions (if that is an issue - i.e. you do not want to grant SELECT on the tables to the users, you only want to grant EXECUTE permission on Sprocs.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-04 : 07:11:01

Other method is to use derived table
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/14/derived-table-new-approach-to-avoid-sql-injection.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources