Limitations of @ - Local variable

Question

Post reply

Limitations of @ - Local variable

karthik M

One Orange Chip

Points: 29139
More actions
October 16, 2008 at 6:02 am

#72961

All,
I have worked on a development project recently. It is deployed now in the QA server for testing.
I have learned few new things about local variable. It may be simple, But it is new to me.
I have identified that local variable won't work with the following clause.
1) IN Clause
2) ORDER BY Clause
I know there may be lot of limitations.
It would be appreciable if anybody share their experience about local variable.
🙂
My intention is to know few more limitations.
karthik

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

rosh-396756 SSCrazy Points: 2338 More actions · Answer 1

I didnt get the local variable not working with IN clause....

Glen SSCertifiable Points: 6727 More actions · Answer 2

Excerpt from code published on SQLServerCentral:

ORDER BY

CASE @sort

WHEN 1 THEN

(CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)*-1

WHEN 2 THEN

(CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.user_seeks)+CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.unique_compiles))*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_total_user_cost)*CONVERT(Numeric(19,6), sys.dm_db_missing_index_group_stats.avg_user_impact/100.0)

ELSE NULL

END

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Not entirely accurate:

[font="Courier New"]USE NorthWind

DECLARE @A TABLE(

NameLetter VARCHAR(20))

INSERT INTO @A(NameLetter)

SELECT 'M' UNION ALL

SELECT 'J'

DECLARE @B VARCHAR(20)

SET @B = 'R'

DECLARE @C VARCHAR(20)

SET @C = 'First'

SELECT *

FROM Employees

WHERE LEFT(FirstName,1) IN (@B)

OR LEFT(FirstName,1) IN (SELECT * FROM @A)

ORDER BY CASE WHEN @C = 'First' THEN FirstName

ELSE LastName

END[/font]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Glen,

We can use like you mentioed in your post.

But the below one won't work.

drop table #tmp

go

Create table #tmp

(

eno varchar(1),

ename varchar(25)

)

go

insert into #tmp

select '1','Karthik'

union

select '2','Keyan'

union

select '3','Mani'

Declare @Column varchar(5)

select @Column = 'ename'

select * from #tmp order by @Column

I got the below error message.

------------

Variables/Parameters are not allowed in the ORDER BY clause.

------------

karthik

rosh-396756 SSCrazy Points: 2338 More actions · Answer 5

and what is the problem with local variable and IN Clause?

Glen SSCertifiable Points: 6727 More actions · Answer 6

select * from #tmp order by

case @Cola

when 'eno' then eno

else ename

end

karthik M One Orange Chip Points: 29139 More actions · Answer 7

Well, Execute the below one.

drop table #tmp

go

Create table #tmp

(

eno varchar(1),

ename varchar(25)

)

go

insert into #tmp

select '1','Karthik'

union

select '2','Keyan'

union

select '3','Mani'

Declare @eno varchar(5)

select @eno = '1,2'

select * from #tmp where eno in (@eno)

karthik

RP_DBA SSCertifiable Points: 5116 More actions · Answer 8

There's always a work around!

drop table #tmp

go

Create table #tmp

(

eno varchar(1),

ename varchar(25)

)

go

insert into #tmp

select '1','Karthik'

union

select '2','Keyan'

union

select '3','Mani'

Declare @Column varchar(5)

, @sql varchar(500)

select @Column = 'ename'

set @sql = 'select * from #tmp order by ' + @Column

exec (@sql)

_____________________________________________________________________
- Nate

@nate_hughes

karthik M One Orange Chip Points: 29139 More actions · Answer 9

Glen,

You are correct !

My point is, we can't use local variable in the order by clause directly.

do you agree ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Glen/RP,

I do agree with you. Both of you gave the work around for the problem.

is it possible to use like below

select * from emp order by @column. This is what i am saying.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 11

And...I have implemented both your method in that project depends on the situation.

But what i am saying is there is no direct way to use the local variable in an ORDER BY clause.

Both of you agree ?

karthik

rosh-396756 SSCrazy Points: 2338 More actions · Answer 12

u can.....

drop table #tmp

go

Create table #tmp

(

eno varchar(1),

ename varchar(25)

)

go

insert into #tmp

select '1','Karthik'

union

select '2','Keyan'

union

select '3','Mani'

Declare @eno varchar(10)

declare @STR varchar(100)

set @eno = '1'

select * from #tmp where eno in ( @eno)

The problem was the value set for @eno. Hence dynamic sql was the solution for the scenario that you stated.

karthik M One Orange Chip Points: 29139 More actions · Answer 13

Rosh,

As you stated the below code will work for single value.

Declare @eno varchar(10)

declare @STR varchar(100)

set @eno = '1'

select * from #tmp where eno in ( @eno)

But if am going to use single value i will use = instead of IN clause.

As my requirement is to check more than one value, i used IN clause. So Again as you said, Dynamic sql may be the solution or should be the solution.

karthik

rosh-396756 SSCrazy Points: 2338 More actions · Answer 14

my reply has come a little late now... so I might seem out of context now.

Karthik, my only argument was there is no problem with local variables and IN clause and ORDER clause.....always a workaround....