Solved

Return a value from a query inside a store procedure

Posted on 2007-04-03
5
186 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

617 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