Store an exec(@query) in variable

Question

Store an exec(@query) in variable

agh100

Ten Centuries

Points: 1139
More actions
July 1, 2009 at 6:31 am

#133901

Hi there,
As far as I can see, there is no way to store an exec query in a variable. What I would like to is this:
SELECT @Store = (EXEC (@query))
I know that this syntax is correct. But I am sure you know what it is I want.. Is this somehow possible?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65143 More actions · Answer 1

Without seeing the query it is difficult to give an answer.

However, assuming the query returns single value you could use a temp table to hold the result and then select from that or use sp_executesql to set the variable from the result of the query.

Far away is close at hand in the images of elsewhere.
Anon.

joeroshan SSChampion Points: 10377 More actions · Answer 2

Can you please give a little more info on what you are trying to achieve?

@store='Exec ('+@sql+')'

The above is not what you are looking for. Is it?

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

naresh.patel3386 SSC Veteran Points: 209 More actions · Answer 3

You can use OutPut variable in such cases...

eg.

Declare @outputvar1 as Int

Exec usp_yoursp @pr1,@pr2,@outputvar1 Output

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

agh100 (7/1/2009)
But I am sure you know what it is I want.. Is this somehow possible?

I don't quite know what you want. Could you explain more.

Do you want single values from the query? If so, look at sp_executesql which allows parameters to be passed in and out of dynamic SQL

Do you want a result set? If so, look at the Insert ... Exec syntax to insert the results into a table.

Do you want something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

agh100 Ten Centuries Points: 1139 More actions · Answer 5

Sorry... What I would like to do is:

- To make my stored procedure return an output variable with the result of the EXEC (@query).. It returns only one value, the number of rows from table X...

If the result can be stored in a temp table, that is fine as well, I just dont know how 😉

CREATE PROCEDURE [dbo].[sp_GetOverallStatus] @statusid int

AS

DECLARE

@tablename varchar(200),

@query varchar(1000)

BEGIN

SET NOCOUNT ON;

SELECT @tablename = (SELECT Tablename FROM dbo.Status WHERE StatusID = @statusid)

SELECT @query = 'SELECT COUNT (*) FROM dbo.'+@tablename+''

EXEC (@query)

END

Mark Cowne One Orange Chip Points: 26967 More actions · Answer 6

Use sp_executesql with OUTPUT variables

CREATE PROCEDURE [dbo].[sp_GetOverallStatus] @statusid int, @rowcount int OUTPUT

AS

DECLARE

@tablename varchar(200),

@query varchar(1000)

BEGIN

SET NOCOUNT ON;

SELECT @tablename = (SELECT Tablename FROM dbo.Status WHERE StatusID = @statusid)

SELECT @query = 'SELECT @rowcount=COUNT (*) FROM dbo.'+@tablename+''

EXEC sp_executesql @query , N'@rowcount INT OUTPUT' , @rowcount OUTPUT

END

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Untested, but should do as you want.

CREATE PROCEDURE [dbo].[GetOverallStatus] @statusid int, @Rows int OUTPUT

AS

DECLARE

@tablename varchar(200),

@query nvarchar(1000)

BEGIN

SET NOCOUNT ON;

SELECT @tablename = Tablename FROM dbo.Status WHERE StatusID = @statusid

SELECT @query = 'SELECT @RowCount = COUNT (*) FROM dbo.['+@tablename+']'

sp_execute @query, '@RowCount int OUTPUT', @RowCount = @Rows OUTPUT

END

GO

DECLARE @Row_Count int

EXEC GetOverallStatus @status = 1, @Rows = @Row_Count OUTPUT

SELECT @Row_Count

Please note that it's not recommended to prefix procedures with sp_ as that's the designation that SQL Server uses for system procedures.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

David Burrows SSC Guru Points: 65143 More actions · Answer 8

:blink: Mark beat me to it, but still my version

CREATE PROCEDURE [dbo].[sp_GetOverallStatus] @statusid int, @count int OUTPUT

AS

DECLARE @query nvarchar(1000)

BEGIN

SET NOCOUNT ON;

SELECT @query = 'SELECT @count = COUNT(*) FROM dbo.' + Tablename FROM dbo.Status WHERE StatusID = @statusid

EXECUTE sp_executesql @query,N'@count int OUTPUT',@count OUTPUT

END

DECLARE @count int

EXECUTE sp_GetOverallStatus 0,@count OUTPUT

SELECT @count

Far away is close at hand in the images of elsewhere.
Anon.

agh100 Ten Centuries Points: 1139 More actions · Answer 9

Thanks very much everyone for the input... It has been very usefull...

Kind regards,

Anders Hansen