CODEWITHSUNDEEP
sqlsql-server
Jamesla
Jamesla

Reputation: 1408

Cannot access if exists select variable

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

Upvotes: 0

Views: 118

Answers (3)

D Stanley
D Stanley

Reputation: 152624

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.

Upvotes: 2

DeanOC
DeanOC

Reputation: 7282

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

Upvotes: 0

John Bingham
John Bingham

Reputation: 2006

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;

Upvotes: 2

Related Questions