March 29, 2012 at 1:55 pm
I am working on a SQL query that takes a variable to base the join on. The same variable is used for ORDER BY clause. They had ensured earlier that there were would be no sort order (ASC or DESC) defined in the parameter passed. So I was modifying the query to work for paging and sorting on a UI grid and was surprised to find that the query does not break if I pass the value for variable as "col1 DESC".
Here's how the query looks
select a.*
from tableA a
left join tableB b on b.ColA = @variable
If I do set @variable = 'col1 desc' it works. I expected it to throw some error.
Can anyone explain what's going on here?
Now that I've written the question, to me it seems like this isn't a join but more of a WHERE clause. Am I right?
Thanks.
March 29, 2012 at 2:04 pm
rjv_rnjn (3/29/2012)
I am working on a SQL query that takes a variable to base the join on. The same variable is used for ORDER BY clause. They had ensured earlier that there were would be no sort order (ASC or DESC) defined in the parameter passed. So I was modifying the query to work for paging and sorting on a UI grid and was surprised to find that the query does not break if I pass the value for variable as "col1 DESC".Here's how the query looks
select a.*
from tableA a
left join tableB b on b.ColA = @variable
If I do
set @variable = 'col1 desc'it works. I expected it to throw some error.Can anyone explain what's going on here?
Now that I've written the question, to me it seems like this isn't a join but more of a WHERE clause. Am I right?
Thanks.
Not really sure why you are using a left join to tableB but nothing is in the select list. Syntactically this will work with no problem but I don't think it is doing what you think it it. It will return b rows where b.ColA = 'col1 desc'
Your query does not have an order by so if they were returned in the correct order it was by chance.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 2:08 pm
Of course your join also has nothing to actually join tableA and tableB. :w00t:
Here is an example of what is happening.
declare @variable varchar(20) = 'col1 desc'
create table #tableA
(
SomeValue varchar(10)
)
create table #tableB
(
ColA varchar(10)
)
insert #tableA select 'MyValue'
insert #tableB select 'Your Value' union all select 'col1 desc'
select a.*
from #tableA a
left join #tableB b on b.ColA = @variable --really should have something to correlate the tables in your join
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 29, 2012 at 2:51 pm
Sean Lange (3/29/2012)
Of course your join also has nothing to actually join tableA and tableB. :w00t:
Thanks for confirming.
I simplified the query that is in there but this had me pondering for a while and thought should ask somebody.
And no I did not commit the crime and I can not re-write that. It's a strict no-no where I work. I will have to make do with it. :angry:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply