passing value to the variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

passing value to the variable

Author

Topic

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-09-27 : 03:03:45

Hi all,
CREATE TABLE INTTEST(COL1 INT IDENTITY(1,1),COL2 NVARCHAR(255) DEFAULT 'SOMEVALUE')
INSERT INTO INTTEST
VALUES(DEFAULT)
DECLARE @V INT
SELECT @V=COL1 FROM INTTEST
PRINT @V

DROP TABLE INTTEST
SELECT * FROM INTTEST

I am inserting multiple values to the table and Passing COL1 value
to my variable.

The integer variable is picking some random value rather than failing.
Any thoughts would be appreciated. Because of this behaviour,I am running into some issues.

Thanks!
Raju
http://www.trickylife-trickylife.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-27 : 03:19:59

Why should it fail?

SELECT @V = Col1 FROM INTTEST

gets a value (in no particular order) from the table, just as you wanted, because you add multiple values at the same time.

Explain to us what you really are looking for.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-27 : 03:21:31

If you want to get some particular value (not random), you will have to provide criteria for it.

Select @V = col1 from IntTest where id = 1

Otherwise, you are bound to get random values.

Harsh Athalye
India.
"Nothing is Impossible"

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-09-27 : 11:32:49

In the stored procedure,I have the scenario where expected value from the downstream is unique distinct value.
Some times I get multiple values and stored procedure getting executed with some random values.

I guess I have to make one more check for the data to validate the input values.

Thanks!
Raju
http://www.trickylife-trickylife.blogspot.com/

vgr.raju
Yak Posting Veteran

95 Posts

Posted - 2006-09-27 : 13:39:19

Still wondering ,I am assigning multiple values to the int variable.
It should fail,stating you can not assign multiple values ..
Why it is picking some random value??
Any thoughts would be really appreciated

Thanks!
Raju
http://www.trickylife-trickylife.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-28 : 04:10:29

quote:
Originally posted by vgr.raju

Still wondering ,I am assigning multiple values to the int variable.
It should fail,stating you can not assign multiple values ..
Why it is picking some random value??
Any thoughts would be really appreciated

Thanks!
Raju
http://www.trickylife-trickylife.blogspot.com/

It will not fail simply because Select statement assigns last returned value by the select statement to the variable..So if you select returns output like:

col1 col2
--------------
1 aaa
2 bbb
3 ccc

It will simply assign last value (i.e. ccc) to the variable...but you will definitely get error if u try something like this with SET statement:

set @var = (select col1 from tbl)

Harsh Athalye
India.
"Nothing is Impossible"

Kristen
Test

22859 Posts

Posted - 2006-09-28 : 05:20:11

"It will simply assign last value (i.e. ccc) to the variable"

Being pedantic you can only guarantee to get "ccc" if you put an ORDER BY on it - the order may otherwise change from one execution to another - depending on what data happens to be lying around in Cache - which can be a hard bug to find!)

Couple of possibly solutions:

1) Harsh's
2) Check @@ROWCOUNT after the task
3) Use SELECT TOP 1 (with an ORDER BY to enforce consistency)
4) Use MIN(MyColumn) - this guarantees a single value, but rather masks the fact that somehow multiple values have gotten into the data stream!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-28 : 08:11:04

Whenever you assign value to a variable, make sure the query returns only one value
Otherwise you may get what you dont want to get

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources