Where clause with @strSQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Where clause with @strSQL

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-01-07 : 20:41:41

Hi,

Is is possible to create a where clause using a variable,
like for example:

Declare @strSQL varchar(1000)
set @strSQL = 'companyID = 2'

Select * from customers where @strSQL

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-07 : 21:36:37

No. Unless you are using Dynamic SQL.

see The Curse and Blessings of Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 02:14:47

quote:
Originally posted by infodemers

Hi,

Is is possible to create a where clause using a variable,
like for example:

Declare @strSQL varchar(1000)
set @strSQL = 'companyID = 2'

Select * from customers where @strSQL

Thanks!

Seems a better way for SQL Injection

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-08 : 02:19:49

http://xkcd.com/327/

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-08 : 02:28:04

quote:
Originally posted by infodemers

Hi,

Is is possible to create a where clause using a variable,
like for example:

Declare @strSQL varchar(1000)
set @strSQL = 'companyID = 2'

Select * from customers where @strSQL

Thanks!

why do you want to pass conditions like this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-08 : 02:43:41

quote:
Originally posted by khtan

http://xkcd.com/327/

KH
[spoiler]Time is always against us[/spoiler]

Madhivanan

Failing to plan is Planning to fail

infodemers
Posting Yak Master

183 Posts

Posted - 2010-01-08 : 08:05:13

Because depending on what the value is passed to the stored procedure, the where clause may need to be changed.

quote:
Originally posted by visakh16

quote:
Originally posted by infodemers

Hi,

Is is possible to create a where clause using a variable,
like for example:

Declare @strSQL varchar(1000)
set @strSQL = 'companyID = 2'

Select * from customers where @strSQL

Thanks!

why do you want to pass conditions like this?

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-08 : 08:36:52

Can you make yoru where clause sort of conditional...like:

Select * From Customers
Where
(companyId = 2 and condition2_1 and condition2_2 .... condition2_N)
or
(companyId = 3 and condition3_1 and condition3_2 .... condition3_N)
or
(companyId = 4 and condition3_1 and condition3_2 .... condition3_N)
....
or
(companyId = M and conditionM_1 and conditionM_2 .... conditionM_N)

this would get around your method as long as you were ensured one of the company IDs.

infodemers
Posting Yak Master

183 Posts

Posted - 2010-01-08 : 08:41:10

I get what you mean and I will give it a try....
Thanks for your suggestion!

quote:
Originally posted by DP978

Can you make yoru where clause sort of conditional...like:

Select * From Customers
Where
(companyId = 2 and condition2_1 and condition2_2 .... condition2_N)
or
(companyId = 3 and condition3_1 and condition3_2 .... condition3_N)
or
(companyId = 4 and condition3_1 and condition3_2 .... condition3_N)
....
or
(companyId = M and conditionM_1 and conditionM_2 .... conditionM_N)

this would get around your method as long as you were ensured one of the company IDs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources