SQL server define local variable

Question

I have the following query in SQL Server

SELECT name,id FROM [someDatabase] 
where id like 'testName'

Now I want to use name and id in another query and define name as NAME and id as ID

SELECT DISTINCT vision FROM [someDatabase] WHERE name = NAME 

And id = ID

I'm not familiar with SQL server syntax. How do I define these two as local variables?

Thanks in advance.

Jeff Hornby · Accepted Answer · 2014-07-08 14:48:41Z

1

You need to declare variables to hold the data first:

DECLARE @name VARCHAR(100),
        @id   INT

Then you can assign to these variables in your select statement. Note that this query will not produce any output.

SELECT @name = name,@id = id FROM [someDatabase] 
where id like 'testName'

Finally you can use these values in your query

SELECT DISTINCT vision FROM [someDatabase] WHERE name = @name 
And id = @id

answered Jul 8, 2014 at 14:48

Jeff Hornby

13.8k4 gold badges42 silver badges65 bronze badges

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

Comments

russ · Accepted Answer · 2014-07-08 14:52:44Z

0

SELECT DISTINCT vision FROM [somedatabase] 
WHERE name IN (SELECT name FROM [someDatabase] where id like 'testName')
AND id IN (SELECT id FROM [someDatabase] WHERE id LIKE 'testName')

answered Jul 8, 2014 at 14:52

russ

6244 silver badges7 bronze badges

4 Comments

Djizeus Over a year ago

This does not answer the question

russ Over a year ago

True but it should give the result he is after presupposing that the IDs are unique and LIKE 'testname' only produces one ID.

Sean Lange Over a year ago

Why hit the same table 3 times when once will do it? Why bother using like with no wildcards?

russ Over a year ago

Is it the same table? I thought he was querying the "vision" column from table_2 based on the id and name resulting from a query to a table_1, he doesn't exactly say. I was using LIKE because anders did in his question so I assume he needs the facility for a boolean variable here...?

Collectives™ on Stack Overflow

SQL server define local variable

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related