Solved

Return a value from a query inside a store procedure

Posted on 2007-04-03
5
181 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 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