Limitations of @ - Local variable

Question

Limitations of @ - Local variable

Viewing 15 posts - 16 through 30 (of 31 total)

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

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

Rosh,

Thats what i am also saying...There is a workaround...But there is no direct way to use the local variable in IN and ORDER BY clause.

Can we say it as limitations of local variable ?

karthik

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

Rosh,

will you see your back head directly ? 😛

But there is always a workaround.

I hope all of us know it.

We can see through mirror. 😀

Just kidding.

karthik

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

looks like you have decided that it is a limitation...

so be it...

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

Karthik, so are you gonna say that your head is a limitation...:)

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

No....our eyes has the limitation.... :Whistling:

do you agree or not ?:D

karthik

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

is there any other command or clause that won't work directly with local variable ?

karthik

Glen SSCertifiable Points: 6737 More actions · Answer 7

It is not a limitation of a local variable. It is a limitation of ORDER BY clause.

(And in this case scenario it is "granted by design").

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

The from clause

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

karthikeyan (10/16/2008)
Glen,
You are correct !
My point is, we can't use local variable in the order by clause directly.
do you agree ?

Without using dynamic SQL, that's correct.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

If you want multiple values in the list format you stated, you cannot. However, if you convert it to a local table variable and use that instead, you can accomplish exactly what you wanted without dynamic sql. Variables are always set to a single value, setting it to a list isn't really logical. See my examples above for both ways.

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]

Sergiy SSC Guru Points: 110208 More actions · Answer 11

karthikeyan,

limitation is not in variables, limitation in your knowlege of the language.

Not sure about juniors, but even medium level developers (developers, not system engineers) suppose to know the difference between values and identifiers.

And after all those training given to you on Tally table you still trying to do IN (@var) things.

I'm feel sorry for Jeff Moden who spent so much time and effort on educating you.

As I told him from the beginning, it was for nothing.

You need learn to learn first.

_____________
Code for TallyGenerator

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

Not sure about juniors, but even medium level developers (developers, not system engineers) suppose to know the difference between values and identifiers.

As a senior, you shouldn't discourage junior or medium level developers. You should know how to motivate peoples. Thats why the difference between senior and junior. Just you think where you stand.

karthik

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

And after all those training given to you on Tally table you still trying to do IN (@var) things.

I am not asking how to implement Tally table to replace IN(@var). If i asked that question, your point is valid.

Can you read the topic once again ? I haven't asked 'How to replace IN(@var) with tally table logic ?' , Suppose if i asked that question, again your point is valid. But the topic is 'Limitations of @ Local variable'.

My intention is to know the limitation of local variable or as some veteran developer said limitations of ORDER BY. I dont know how you are linking tally table training with this question.

karthik

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

I'm feel sorry for Jeff Moden who spent so much time and effort on educating you.
As I told him from the beginning, it was for nothing.

Sergiy,

My friend....Don't feel....Also don't insert your head unnecessarily in all the matter. Okay !

I have never wasted his time and effort spent on educating me.

Because i have implemented his Tally table concept in more than 20 stored procedures so far. I hope the list should grow in the future. I am ready to show all those procedures at offline, if you don't believe me.

And finally, how you have decided 'it was for nothing...' Don't give unnecessary comments without knowing completely. It will harm your life. It is my personal advice to you. It leads you to lot of problem.

karthik

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

Hey..Hey.....Sergiy

I am not fighting with you, I am ready to take all your comments,suggestions and advices. But there is a way to give all those things. The way you are saying is hurting other peoples.

Try to change the way you are approaching...

Happy weekend 🙂

karthik