select * from xxx where 1=1 ??
-
I have had to modify some old stored procs written by a person who no longer works here and I have seen a few selects using '.... where 1=1 and column1 ....'
What does it do? It looks redundant but I'm not sure if this is something that speeds up searches or something.
Can anybody explain this to me?
-
It will return all the rows in a table. I've seen it being used when "Depending upon condition certain where clause is applied"
-
Thanks for your reply.
Right, it returns the whole table, but a select without a where clause does the same thing. So what is it then for? It doesn't make any sense to me.
-
It makes the concatenation work easier. You don't have to check if a condition has been set before adding another conidtion dynamically. Query wise, that condition is checked once then ignored so speed is not affected.
-
Hi Ninja -
Could you please give an example? I'm not following how this makes concatenation work easier.
Thanks,
Susan
-
say i'm building a dynamic sql query
set @query = 'select * from table where 1=1 '
if @somecondition = 'yes'
begin
set @query = @query + ' and x=2'
end
exec @query
It makes the "and" a given instead of having to test for it. I don't use it much, but another programmer here uses it quite frequently.
-
Here is an example:
Create procedure ssp_findData
@i_UserID int = 0,
@i_DeptID int = 0
as
Begin
set nocount on
Declare @s_SQL varchar(512)
select @s_SQL = 'select * from UserDept where 1 = 1'
If @i_UserID <> 0
Begin
select @s_SQL = @s_SQL + ' and UserID = ' + Cast(@i_UserID as varchar(5))
End
If @i_DeptID <> 0
Begin
select @s_SQL = @s_SQL + ' and DeptID = ' + Cast(@i_DeptID as varchar(5))
End
select @s_SQL
set nocount off
End
I don't have to check to see if I need to put a "where" Clause or " and " etc. Hope this helps.
Thanks
Sreejith
-
What Dylan said
.I might add that this is most often used for "complex" search forms where any number of parameters can be entered.
-
Got it! Thanks for the explanation. -
Thanks guys...
It actually is veru useful. I've created some sp with dynamic sql and have been dealing with that 'where used' problem....
It's a lot easier this way.
-
You might want to read this :
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply