I have a problem and don't understand why I cannot get this to work. I will
include the sp below but the gist of it all is I have a field that is an
equation in string format such as Value a/Value b and I have 3 seperate
tables tblFCF1, tblFCF2, tblFCF3 that each hold information that holds
numbers (int) to be plugged into the equation replacing the Value a and
Value b. Now the kicker is the equation and number of values is different
for each one. I don't have a problem replacing the strings with the numbers.
My problem comes in when I want to cast the string as integer so I can get
the result of the equation. The SP below works fine but it returns 6
recordsets and I need it to return row for each fetch loop with the results
of scf1, scf2, scf3 as columns. If anyone knows how I can get one row for
each loop it would be great. Otherwise, I will have to investigate doing
some programming from asp.net passing values in. I don't want to do this
because I like keeping my sql info seperated.Here is the table
info.***********************************************
*Stores information about each of the indicators
*********************************************
CREATE TABLE tblIndicators
(
indicator_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
indicator_name varchar(255) NOT NULL,
cat_id smallint NOT NULL,
fcf1_target varchar(50) NOT NULL,
fcf2_target varchar(50) NOT NULL,
fcf3_target varchar(50) NOT NULL,
equation varchar(255) NOT NULL
)


*************************************
* Stores information about each value in an equation
************************************
CREATE TABLE tblIndicatorValueInfo
(
indicator_id smallint NOT NULL,
v_pos int NOT NULL,
v_label varchar(255) NOT NULL,
v_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED
)

**************************************
* The following 3 tables store the actual values that are plugged into the
equation from tblIndicators for each of the
* manager areas
**************************************

CREATE TABLE tblFCF1
(
indicator_id smallint NOT NULL,
v_id smallint NOT NULL,
input_date smalldatetime NOT NULL,
actual_value int NOT NULL,
item_id int NOT NULL
)

CREATE TABLE tblFCF2
(
indicator_id smallint NOT NULL,
v_id smallint NOT NULL,
input_date smalldatetime NOT NULL,
actual_value int NOT NULL,
item_id int NOT NULL
)

CREATE TABLE tblFCF3
(
indicator_id smallint NOT NULL,
v_id smallint NOT NULL,
input_date smalldatetime NOT NULL,
actual_value int NOT NULL,
item_id int NOT NULL
)

%%%%%%%%%%%%%%%%%%%%%%%
I don't have the relationships set but v_id of the 3 FCF tables are the
foriegn keys of tblIndicatorValueInfo.v_id



if exists (select * from sysobjects where id =
object_id(N'[dbo].[spGetActualValue]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[spGetActualValue]
GO

CREATE Procedure spGetActualValue
@the_date smalldatetime

As



DECLARE @indicator int
Declare @thename varchar(255)
Declare @scf1 as varchar(255)
Declare @scf2 as varchar(255)
Declare @scf3 as varchar(255)
Declare @final as varchar(255)
Declare @eq varchar(255)
Declare @eq2 varchar(255)
Declare @eq3 varchar(255)
Declare @x int
Declare @value_id int
Declare @maxvalue int
Declare @finalvalue int



DECLARE indicator_cursor CURSOR FOR
SELECT indicator_id, indicator_name FROM tblIndicators



OPEN indicator_cursor

FETCH NEXT FROM indicator_cursor
INTO @indicator, @thename

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

Set @x = 1
Set @eq = (Select equation from tblIndicators where indicator_id =
@indicator)
Set @maxvalue = (Select Count(v_id) from tblIndicatorValueInfo where
indicator_id = @indicator)

while @x <= @maxvalue
Begin

Set @value_id = (Select v_id from tblIndicatorValueInfo where indicator_id
= @indicator and v_pos = @x)
Set @eq = Replace(@eq,(Select v_label from tblIndicatorValueInfo
where v_id = @value_id),
(Select SPACE(1) + actual_value + SPACE(1) from tblFCF1
where v_id = @value_id and input_date = @the_date))
Set @x = @x + 1
End




set @scf1 = 'select cast(' + @eq + ' as int) as [scf1]'


Exec(@scf1)


Set @x = 1
Set @eq2 = (Select equation from tblIndicators where indicator_id =
@indicator)
Set @maxvalue = (Select Count(v_id) from tblIndicatorValueInfo where
indicator_id = @indicator)

while @x <= @maxvalue
Begin

Set @value_id = (Select v_id from tblIndicatorValueInfo where indicator_id
= @indicator and v_pos = @x)
Set @eq2 = Replace(@eq2,(Select v_label from tblIndicatorValueInfo
where v_id = @value_id),
(Select SPACE(1) + actual_value + SPACE(1) from tblFCF2
where v_id = @value_id and input_date = @the_date))
Set @x = @x + 1
End


set @scf2 = 'select cast(' + @eq2 + ' as int) as [scf2]'

Exec(@scf2)

Set @x = 1
Set @eq3 = (Select equation from tblIndicators where indicator_id =
@indicator)
Set @maxvalue = (Select Count(v_id) from tblIndicatorValueInfo where
indicator_id = @indicator)

while @x <= @maxvalue
Begin

Set @value_id = (Select v_id from tblIndicatorValueInfo where indicator_id
= @indicator and v_pos = @x)
Set @eq3 = Replace(@eq3,(Select v_label from tblIndicatorValueInfo
where v_id = @value_id),
(Select SPACE(1) + actual_value + SPACE(1) from tblFCF3
where v_id = @value_id and input_date = @the_date))
Set @x = @x + 1
End


set @scf3 = 'select cast(' + @eq3 + ' as int) as [scf3]'

Exec(@scf3)




FETCH NEXT FROM indicator_cursor
INTO @indicator, @thename

End

CLOSE indicator_cursor
DEALLOCATE indicator_cursor



GO

The current output in Query Analyzer looks like such (fake numbers for demo
purposes) which is multiple recordsets.

scf1
50

scf1
65

scf1
70scf120scf140scf190I would like to have one recordset with multiple records
for each fetch statement.
scf1 | scf2 | scf3
50 65 70
20 40 90

Thanks Marty U

[quoted text, click to view]

Try changing the Exec(@scf1)...Exec(@scf3) to INSERT EXEC statements
and store the results in a temp table. At the end, use CASE to do the
crosstab/pivot. Something like:

create table #R (whoAmI char(4), result int, someCol int)
....
insert into #R exec(@scf1)
insert into #R exec(@scf2)
insert into #R exec(@scf3)
....
select
scf1=max(case whoAmI when 'scf1' then result end),
scf2=max(case whoAmI when 'scf2' then result end),
scf3=max(case whoAmI when 'scf3' then result end)
from #R