Solved

Return a value from a query inside a store procedure

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PK numbers not follow 8 61
SQL - sum months, quarter, YTD 9 91
Help with SQL - TOP 10 by date and by group 13 34
CROSS APPLY 4 45
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

861 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now