Solved

Return a value from a query inside a store procedure

Posted on 2007-04-03
5
183 Views
Last Modified: 2010-03-19
Hello there experts,

How can i do something like this:
A store procedure where i send a query and the store procedure returns the value from the query i sent into a variable.

Exe.:
declare @MyVal as float
SET @MyVal = sp_execSqlValue('SELECT SUM(value) FROM table')
SELECT @MyVal

Is this possible?
0
Comment
Question by:justaphase
  • 3
5 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18845402
I don't understand.  This does the same as your example:

Create Procedure MyProc
AS
 SELECT SUM(Value) From table
GO
0
 
LVL 42

Expert Comment

by:dqmq
ID: 18845428
Or, maybe this is what you are thinking:

CREATE PROCEDURE myProc (@Query)
AS
EXEC (@Query)
GO

But, it is risky, and I still don't understand the benefit of doing such a thing.  
0
 
LVL 1

Author Comment

by:justaphase
ID: 18849402
The benefit is that i have a field in table that contains a query and i need to execute it and retrieve the value into a variable.
New example:
DECLARE @MyQuery AS nchar(200)
DECLARE @MyVal AS float

SET @MyQuery = (SELECT queryfield FROM mytable WHERE id=1) <-- The query inside the queryfield returns one numeric value
SET @MyVal = EXEC (@MyQuery) <-- This is the problem, i can't do it like this
0
 
LVL 39

Expert Comment

by:appari
ID: 18862536
try this

DECLARE @MyQuery AS nchar(200)
DECLARE @MyVal AS float

SET @MyQuery = (SELECT queryfield FROM mytable WHERE id=1) <-- The query inside the queryfield returns one numeric value

create table #testTab(col1 varchar(200))
insert into #testTab
EXEC (@MyQuery)

select * from #testTab
select @MyVal  = col1 from #testTab

drop table #testTab

0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 19002501
It's a long-shot, but maybe this:

DECLARE @MyResult VARCHAR(8000)
EXEC ('SET @MyResult =(' + @Query + ')')


0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Stored procedure 4 36
Set the max value for a column 7 40
Delete from table 6 48
How to place a condition in a filter criteria in t-sql? 12 77
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question