I need values return through the two statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I need values return through the two statement

Author

Topic

atulbharadwaj
Starting Member

11 Posts

Posted - 2009-12-15 : 13:44:03

There is a situation where I need to calcuated two data within the stored procedure. And the sum of these two data should

return from stored procedure.Right now I am not concentrating on return value.

My problem is that I need the values return through the two select statement within @v1 and @v2, but when i execute this

procedure it returns me the select statement (string) within these variable. Please suggest me the appropriate way to do this.
I will appriciate any response. it will help me to go further into my solution.

ALTER PROCEDURE [dbo].[WeightageInASI]
@Quarter varchar(10),
@Sector varchar(100),
@CurrentYear varchar(10)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Weightagevalue float
DECLARE @v1 float, @v2 float, @v3 float

select @v1 = 'SELECT isnull(sum(Q.'+ @Quarter +'),0) as '+ @Quarter +' FROM CompanyClassification c INNER JOIN
QuarterlyCapitalisation Q ON C.BSC_CODE=Q.BSC_CODE WHERE
C.SECTOR = ' + Char(39) + +' ' + @Sector + Char(39) +' AND Q.'+ @Quarter +' !=0 AND YEAR= '+ Char(39) + @CurrentYear +

Char(39) +''
exec(@v1)

select @v2 = 'SELECT isnull(sum(Q.'+ @Quarter +'),0) as '+ @Quarter +' FROM CompanyClassification c INNER JOIN
QuarterlyCapitalisation Q ON C.BSC_CODE=Q.BSC_CODE WHERE
C.SECTOR = ' + Char(39) + 'Not In ASI' + Char(39) +' AND Q.'+ @Quarter +' !=0 AND YEAR= '+ Char(39) + @CurrentYear + Char(39)

+''
exec @v2
set @v3 = @v1 * 100 / @v2

end

atul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-16 : 03:31:43

looks like your table is not normalized at all. That's why you need to pass in the column name.

Can you normalize your table ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources