Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jaroslaw Stoklosa » SELECT setter
Add to briefcase
17 posts, Page 1 of 2
12»»

SELECT setter Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
stoklosa
Posted Wednesday, February 10, 2010 9:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, September 29, 2012 6:38 AM
Points: 276, Visits: 21
Comments posted to this topic are about the item SELECT setter
Post #863801
Stewart "Arturius" Campbell
Posted Wednesday, February 10, 2010 10:25 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 3,129, Visits: 4,312
Usig SELECT is a versatile method of allocating values to multiple variables as well.
good "back to basics" question.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #863830
Fal
Posted Wednesday, February 10, 2010 11:07 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 10:05 PM
Points: 584, Visits: 1,571
A good question to remind us of something we all might forget: WHERE isn't just used after FROM.

S.
Post #863848
SQLRNNR
Posted Wednesday, February 10, 2010 11:18 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:25 AM
Points: 18,754, Visits: 12,337
Agreed. Good back to basics question.


Thanks


Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #863852
Open Minded
Posted Wednesday, February 10, 2010 11:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555
I got the answer right because I was looking at the logic, 'where 1=0'.

But when I changed it to 'where 1=1', the output came out as 0.

If a 'A SELECT statement that doesn't return results doesn't set a variable to anything.', then why didn't it remain at 1 when I changed the logic to 'where 1=1' ?

I hope I don't sound terrible naive about this. Below is the code I used to check the veracity of the statement:

DECLARE @i INT

SELECT @i = 1
SELECT @i = 0 where 1=0
SELECT @i as i

SELECT @i = 1
SELECT @i = 0 where 1=1
SELECT @i as i
SELECT @i

May I ask for further enlightenment? Thank you.
Post #863858
Stewart "Arturius" Campbell
Posted Thursday, February 11, 2010 12:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 3,129, Visits: 4,312
If a 'A SELECT statement that doesn't return results doesn't set a variable to anything.', then why didn't it remain at 1 when I changed the logic to 'where 1=1' ?


if you run the following statements:
select 'Has no value' where 1 = 0
select 'Has value' where 1 = 1

you will see that the first statement does not return any rows, but the second does.
in this vein, the question posted has "where 1 = 0", which does not generate a result set, which will not change the variable's value.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #863863
Open Minded
Posted Thursday, February 11, 2010 12:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 11, 2013 12:58 AM
Points: 174, Visits: 555
Thanks! I hope I got this right: if the WHERE clause will create a result or boolean TRUE (1), then the SELECT part makes a change, if the SELECT part happens to be changing the value of a variable.

It will be just like reversing an IF-THEN statement, like THEN-IF. Well, I hope I got that analogy right also.
Post #863868
Stewart "Arturius" Campbell
Posted Thursday, February 11, 2010 1:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 8:31 AM
Points: 3,129, Visits: 4,312
That is correct.
Wherever the predicate equates to true, an action will take place.

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #863908
Steve Jones - SSC Editor
Posted Thursday, February 11, 2010 10:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 11:20 AM
Points: 31,437, Visits: 13,752
I suspected no assignment, but I had these visions of C where = means something happens.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #864180
john.arnott
Posted Thursday, February 11, 2010 11:16 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:57 PM
Points: 1,491, Visits: 3,008
I'm proud of my wrong answer ("NULL") as it shows I thought it through, even if incorrectly, before triying the script in SSMS. <rant on> I can't imagine that 85% of respondants actualy knew the result of the SELECT .... where the condition is false would not be assigned to the variable. </rant>

Meanwhile, for those interested in seeing this behavior in SSMS....
DECLARE @i INT

SELECT @i = 0
SELECT @i AS i			--Returns 0
SELECT @i = 1 where 1=0		--Null; assignment ignored
SELECT @i AS i			--Returns 0
SELECT @i = (SELECT 2 where 1=0)--Assign result of subSelect with FALSE condition  ***
SELECT @i AS i			--Returns NULL
SELECT @i =	3 where 1=1	--Condition is true, so assignment works
SELECT @i AS i			--Returns 3
SELECT @i = (SELECT 4 where 1=1)--Assign result of subSelect with TRUE condition  ***
SELECT @i AS i			--Returns 4
Post #864201
« Prev Topic | Next Topic »

Add to briefcase
17 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.