Getting the record(s) returned from exec

[BrianMarsh]

I am building a string from info found in a database. I then execute that string.

execute(select * from whatevertable);

in query analyzer it works find and returns the value i need. my question is, how can i use the values it gives me inside the stored procedure that it is in. I cannot figure out a way to get the info...there has to be a way. I tried select @whatever = execute(statement);
doesnt work =
thanks in advance for the help

 

[bperry]

If I am following you correctly, your SP actually can't do much with it. i.e. The SP will 'return' the recordset to the calling client (ASP, VB, Query Analyzer...), but the SP itself typically doesn't work with the new recordset.

If you post your SP, along with some comments about what you are trying to achieve, probably someone here will be able to make some suggestions for you.

bp

 

[BrianMarsh]

Basically, I have a field in a table that has a calculation with a field name. "1.5*Census.Pay1/12" So thats 1.5 times the field Census.Pay1 divided by 12. Now, based on the number that is returns, which is the monthly amount of pay, I need to determine the cost. The cost is another field that is just a decimal number. So its the decimal cost times the amount that was determined by the field that is evaluated. I need this to happen entirely in SQL Server. I can do stored procedures inside of stored procedures if necessary.

Code:
Select @BenefitAmount = "1.5*Census.Pay1/12"
Execute ("Select " + @BenefitAmount + " as Amount3 from census where trackingid=" + @TrackingID)

That returns the correct value.

But, I now need that value to make another calculation inside sql.

Thanks again for the help.

 

[bperry]

>>But, I now need that value to make
>>another calculation inside sql.

Well, if we can see that other calculation, then we can recommend what to do.

A couple of different thoughts (based on what I'm hearing so far), re: how to use the first calculation.

There are (at least) two different ways to go, depending on what work we are try to accomplish.

(1) Set up the first value as a local variable in the SP.
For example:

declare @BenefitAmount decimal (6,2) -- or int, whatever
Select @BenefitAmount = 1.5*Census.Pay1/12 from Census
where trackingid = @TrackingId

This will set up @benefitamount as a local variable. Now we can continue on in the same SP and use the variable in another statement, for example:

INSERT NEWTABLE (PkId, Amount) VALUES (1, @BenefitAmount)



(2) Depending (again) on how we are setting things up, you can just use the whole original statement as an embedded subquery.
For example,

Select
Col1,
Col2,
(Select 1.5*Census.Pay1/12 from Census
where trackingid = @TrackingId) as Amount3,
col3,
col4
From SomeTable

----------------------------
Hoping this gives you some ideas,
bp

 

[tlbroadbent]

NOTE: The following applies only to SQL 7 and higher.

If you need to return a single value from the query statement, you can use the system stored procedure sp_executesql rather than execute command. sp_executesql uses output parameters to return values

Declare
@BenefitAmount nvarchar(20),
@amount3 decimal(8,2),
@sql nvarchar(100)

Select @BenefitAmount = '1.5*Pay1/12'

--Create SQL statment
Select
@sql=N'Select @amt=' + @BenefitAmount +
N' From census Where trackingid=" + @TrackingID

Exec sp_executesql
--SQL statement to be executed
@sql,
--Output variable declaration
N'@amt decimal(8,2) output',
--Output variable (@amt) to be returned to @amount3
@amt=@amount3 output

--Print the result
--You can also use this result in other calculations
Print @amount3 Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[BrianMarsh]

that worked great! can't thank you enuf. one question more tho. what is the N for? pardon my ignorance, i'm very new to SQL and still learning.

 

[BrianMarsh]

i spoke too soon. it looks to do the same thing as if i just executed the statement using execute ("select blah blah&quot. @amount3 prints out blank. the value is not assigned to it. am i doing something wrong? here is my code...

Declare
@BenefitAmount nvarchar(20),
@amount3 decimal(8,2),
@sql nvarchar(100),
@TrackingID nvarchar(6)

Select @TrackingID = '6'
Select @BenefitAmount = '1.5*Census.Pay1/12'
Select @sql = N'Select ' + @BenefitAmount +
N' from census where trackingid=' + @TrackingID

Exec sp_executesql @sql,
N'@amt decimal(8, 2) output',
@amt = @amount3 output

print @amount3

 

[BrianMarsh]

nm, figured it out.

 

[tlbroadbent]

Did you also find that the N converts the string to UNICODE? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

yikes, this thread has me completely buffaloed. Over my head. Is there any way either of you can (briefly) explain to me what's going on?

For example, why does this not bomb?
Select @BenefitAmount = '1.5*Census.Pay1/12'

How does it know what the heck Census.Pay is?

Any comments you might add much appreciated.
bperry

 

[tlbroadbent]

Brian,

Select @BenefitAmount = '1.5*Census.Pay1/12' simply stores a string value in the variable @BenefitAmount. The variable is later used to build the query string that is dynamically executed.
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

okay, rats, I overlooked the quote marks.
Thank you..... that one line has been a thorn in my side since last week. how embarassing.