variable on where clause - 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 2008 Forums

Transact-SQL (2008)

variable on where clause

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-01 : 12:49:26

is it possible to have a variable for the where clause in a select statement. I have a long query which will I will be making into a stored procedure. I will have to make 5 stored procedures because the where statement is different, so i was wondering if something like the following could be done and if so how

declare @Name vatchar(10)
declare @WHO INT
If @Who = 1 then
Begin
set @Name = 'Fred'
End

If @Who = 2 then
Begin
set @Name = 'John'
End

Select * from table where name = @Who

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:03:16

you need a parameter for that. make it procedure with parameter and then use it like below


CREATE PROC yourprocname
@Who int
AS

declare @Name varchar(10)

SELECT @Name = CASE @Who
               WHEN 1 THEN 'Fred'
               WHEN 2 THEN 'John'
               ...
               END

Select * from table where name = @Name
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-01 : 13:15:34

Thanks for the reply, that gave me a good insite now to complicate thins a bit more. what if I need to look at both sides of the where query. for instance

Where DATEDIFF(d, tbCBMTranSet.SubmittedDate, GETDATE()) = 0
Where Month(SubmittedDate) = Month(getdate()) AND Year(SubmittedDate) = YEAR(Getdate())
Where Year(SubmittedDate) = YEAR(Getdate())

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 13:17:20

sorry didnt understand why you need all these conditions?
first condition itself covers the other two within itself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-02-01 : 15:38:29

Let's back up a bit first...

The WHERE clause is just an expression that has to evaluate to TRUE; any other value (False, Unknown) fail. There is nothing that says it has to have anything to do with the tables, columns, etc. Clearly, in the overwhelming majority of cases it does. What that means is that you are free to design any criteria that supports your needs and make use of any resources that are within scope. This includes variable, functions, et al, on either side of the logical operator.

BTW, the same holds true for JOIN logic.

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

Pete_N
Posting Yak Master

181 Posts

Posted - 2012-02-01 : 16:03:51

Sorry guys, maybe trying to throw an idea in instead of the real problem didn't work. ok start again. Right I have 5 stored procedures all exactly the same apart from the 'Where' clause. What i was trying to do is only have the one stored procedure and parm in a parameter to determin which 'where' clause should be applied ifor that execution of the stored procedure. Think i will knock this one on the head and stick with the multiple stored procedures

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-01 : 16:08:13

quote:
Originally posted by Pete_N

Sorry guys, maybe trying to throw an idea in instead of the real problem didn't work. ok start again. Right I have 5 stored procedures all exactly the same apart from the 'Where' clause. What i was trying to do is only have the one stored procedure and parm in a parameter to determin which 'where' clause should be applied ifor that execution of the stored procedure. Think i will knock this one on the head and stick with the multiple stored procedures

you can wrap the code inside same procedure
and instead of using multiple where clause
just put a single where clause and seperate different conditions by AND /OR operators
if you can let us know full set of conditions and parameter values for which they need to be checked for, we can help more

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources