variable containing a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

variable containing a query

Author

Topic

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-10-21 : 21:52:07

Hello,

I would like to store the result of a query into a variable because I've to use it several times and I don't want it to be evaluated each times.

In other words, I would like to write something like this :
DECLARE @query
SET @query = select count(*) from Products where ...
select * from orders
where @query = ... and @query = ...

Is it possible ?

Regards,

mathmax

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 23:58:00

you need dynamic sql for this


DECLARE @query varchar(1000),@sql varchar(8000)
SET @query = 'select count(*) from Products where ...'
SET @sql='select * from orders
where '+@query + ' = ... and ' + @query + ' = ...
'
EXEC (@sql)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-22 : 05:31:06

quote:
Originally posted by mathmax

Hello,

I would like to store the result of a query into a variable because I've to use it several times and I don't want it to be evaluated each times.

In other words, I would like to write something like this :
DECLARE @query
SET @query = select count(*) from Products where ...
select * from orders
where @query = ... and @query = ...

Is it possible ?

Regards,

mathmax

Hi mathmax,

Are you wanting to store the *result* of the query into some variable? Or are you wanting to store the query itself in a variable?

If you need to store the *result* of a aggregate query (or one that only returns 1 row then yes you can do something like this::


DECLARE @rows

-- Using SET
SET @rows = (SELECT COUNT(*) FROM products WHERE <your condition here>)

-- Using SELECT
SELECT @rows = COUNT(*) FROM products WHERE <your condition here>

If you need to store the results of a query that returns a set then you either need to store that into a temp table or a table variable using the usual syntax.

I don't think Visakh has given you what you asked for exactly. He is showing you how to store the query and then execute it dynamically with a call to EXEC() This is certainly very useful in specific situations but it isn't what I think you wanted.

Regards

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 06:19:17

In that case, you need to use a variable if its a single value or use temp table if its a resultset

mathmax
Yak Posting Veteran

95 Posts

Posted - 2008-10-22 : 09:19:48

Thank you for your answers.
Yes I want to store a single value in the variable (always the result of an aggregate function). The proposition of Transact Charlie seems to be good for me. So I mustn't declare the type of the variable, ok.
Else, I was thinking of an other solution. Using a subquery in order to avoid writing several times the same query. For example something like this :

select * from orders
where exists (
select 0 from
(select count(*) MyVar from products where ...) t1
where MyVar = ... and aFunction(MyVar) = ...
)

In that case, I think that the query will be evaluated only once per row of the orders table, isn't it ?
In your mind which is the more efficient solution at execution : Transact Charlie's proposal or using a subquery ?

regards,

mathmax

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-22 : 12:20:30

Sorry -- that was a typo on my behalf :(

Yes you need to declare the type of variable just as normal. So for the result of a COUNT(*) it would be INT (or BIGINT)

Sorry about that.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-22 : 12:23:28

Hi Mathmax,

Sorry I still don't get exactly what you are after in this case.

Can you possibly post some sample data and explain what you need to generate from it. Your pseudo-code looks badly formed to me so I can't tell what you are trying to do.

Regards,

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources