Store an exec(@query) in variable
-
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?
-
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. -
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?
-
You can use OutPut variable in such cases...
eg.
Declare @outputvar1 as Int
Exec usp_yoursp @pr1,@pr2,@outputvar1 Output
-
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
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
-
Use sp_executesql with OUTPUT variables
CREATE PROCEDURE [dbo].[sp_GetOverallStatus] @statusid int, @rowcount int OUTPUTAS
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
-
Untested, but should do as you want.
CREATE PROCEDURE [dbo].[GetOverallStatus] @statusid int, @Rows int OUTPUTAS
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 recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
:blink: Mark beat me to it, but still my version
CREATE PROCEDURE [dbo].[sp_GetOverallStatus] @statusid int, @count int OUTPUTAS
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. -
Thanks very much everyone for the input... It has been very usefull...
Kind regards,
Anders Hansen
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply