Using a Result of Select Query in a Variable

[pagey47]

Just wondered if you could help me, im fairly new to SQL. I can write querys fine but now im looking to use variables in a query.

What i have is the following:

DECLARE @CLIENT varchar(20),
@TABLE_NAME varchar(100)

SET @CLIENT = '1'
SET @TABLE_NAME =

now i want the @TABLE_NAME variable to be the result of the following query:

SELECT TABLE_NAME FROM CUSTOMERS WHERE CUSTOMER_REF = @CLIENT

which returns the result of CLIENT_ABC

so the @TABLE_NAME variable would become CLIENT_ABC

if you could explain how todo this that would be appreciated.

 

[pullesj]

SELECT @TABLE_NAME = TABLE_NAME
FROM CUSTOMERS
WHERE CUSTOMER_REF = @CLIENT

 

[clapag22]

SELECT @TABLE_NAME = TABLE_NAME
FROM CUSTOMERS
WHERE CUSTOMER_REF = @CLIENT

 

[pagey47]

Thanks for those quick replys, can you also use these variables in the FROM clause because its asking me to declare the variable when i have declared it above.

 

[pullesj]

Variables used in FROM clause is OK, I use them frequently with the same syntax noted in the examples above without a problem.

Are you using MS Query Analyzer or some other tool?

 

[pagey47]

DECLARE @CLIENT varchar(20),
@TABLE varchar(255)

SET @CLIENT = '1'
SELECT @TABLE_NAME = TABLE_NAME
FROM CUSTOMERS
WHERE CUSTOMER_REF = @CLIENT

SELECT *
FROM @TABLE_NAME

Above is a example of how i would imagine it would look like, im using the MS Query Analyzer.

Thank you in advance for the help.

 

[pullesj]

Your statement:

SELECT * FROM @TABLE_NAME

is not valid

You can use the @TABLE_NAME variable in other SQL statemtents, however, it is not a table that can be queried.

You could include it in a SELECT statement such as:

SELECT @TABLE_NAME, *
FROM CUSTOMERS
WHERE CUSTOMER_REF = @CLIENT

but not in the manner you noted above.

You could display it (In Q/A, at least) with:

PRINT "Variable @TABLE_NAME " + @TABLE_NAME

or more simply:

PRINT @TABLE_NAME

 

[clapag22]

declare @sql varchar(255)

select @sql = 'select * from ' + @TABLE_NAME

exec(@sql)