Cannot access if exists select variable

Question

I am having trouble finding the correct syntax to make this work?

I have a table with the columns id and color and I want to print out all of the id's for a specific color.

if exists(select id from mytable where color = 'red')
    print id


if exists(select id from mytable where color = 'red')
    print SCOPE_IDENTITY() --which won't work because i'm using select rather than insert

Why are you using PRINT from SQL? SQL isn't designed to easily "print" the results of a query. — D Stanley
– D Stanley, Commented Sep 11, 2013 at 21:47

D Stanley · Accepted Answer · 2013-09-11 21:58:30Z

2

PRINT only prints one value. It looks like you want to loop through the results and print each value, which will require a cursor:

DECLARE @id INT
DECLARE id_cursor CURSOR FOR 
SELECT id from mytable where color = 'red'

OPEN id_cursor

FETCH NEXT FROM id_cursor 
INTO @id

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @id

    FETCH NEXT FROM id_cursor 
    INTO @id
END 
CLOSE id_cursor;
DEALLOCATE id_cursor;

Which seems ridiculous to do in SQL and is why I said in my comment that SQL is not designed to easily print the results of a query - you may be better off returning a result set and letting the consumer print the results.

answered Sep 11, 2013 at 21:58

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jamesla Over a year ago

Ok thats what I was looking for sorry about the confusion I tried to simplify my example and I think I made it to simple but the cursor is exactly what I was looking for, thanks.

John Bingham · Accepted Answer · 2013-09-11 21:25:24Z

2

id isnt defined.

The id in the exists subquery doesnt exist outside that subquery.

You could do this:

declare @id int;
select @id = id from table_name;
if (@id is not null) 
    print @id;

answered Sep 11, 2013 at 21:25

John Bingham

2,0061 gold badge13 silver badges15 bronze badges

3 Comments

Jamesla Over a year ago

Sorry my example wasn't quite right for what I was trying to do, I have just updated it :-)

dcaswell Over a year ago

@jamesla. You still have the problem in your code. ID is a column name, not a variable. You have to declare a variable like John did.

Jamesla Over a year ago

This only prints 1 id rather than all of the id's with the specified color. (not your fault I realised I had omitted this and updated the question after you posted this John).

DeanOC · Accepted Answer · 2013-09-11 21:49:47Z

0

You just need select id from mytable where color = 'red

This will return every id where color is red. If there aren't any, then you will get a single row in the dataset with in the id column

answered Sep 11, 2013 at 21:49

DeanOC

7,3326 gold badges44 silver badges57 bronze badges

2 Comments

Martin Smith Over a year ago

Last sentence isn't correct. If there aren't any, then they will get an empty resultset.

Jamesla Over a year ago

This is technically correct however my example is contrived for the reason that I need to run additional queries using the id, so we need to stick to using print.

Collectives™ on Stack Overflow

Cannot access if exists select variable

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related