Limitations of @ - Local variable

Question

Post reply

Limitations of @ - Local variable

Viewing 14 posts - 16 through 29 (of 29 total)

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

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

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

so be it...

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

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

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

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

do you agree or not ?:D

karthik

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

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

karthik

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

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 6

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 7

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: 110209 More actions · Answer 8

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 9

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 10

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 11

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 12

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

Sergiy SSC Guru Points: 110209 More actions · Answer 13

Karthik,

I can give you set of other "variable limitations".

For example:

SELECT * FROM @Table

SELECT Value as @ValueName

GROUP BY @Column

etc.

All these are actually cases of misuse of language elements typical for beginners.

What means - RTFM!

And it does not matter how many procedures you put Tally table in.

If you cannot recognize the case when you need to use it - you still did not get the concept.

It's not a guess, it's for sure.

_____________
Code for TallyGenerator

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

do you mean to use like below

Create table #tmp

(

eno int,

ename varchar(25)

)

go

insert into #tmp

select 1,'Karthik'

union all

select 2,'Keyan'

union all

select 3,'Ravi'

go

Declare @Name varchar(50)

select @Name = 'Karthik,Keyan'

Select @Name = ',' + @Name + ','

Select eno,ename

from #tmp

where ename in (Tally table logic to split the values based on comma

)

karthik