Thread: Serious Issues
View Single Post
  #8 (permalink)  
Old December 15th, 2009, 03:02 PM
atulbharadwaj atulbharadwaj is offline
Registered User
 
Join Date: Dec 2009
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default calculate resultset of two sql statement

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
Reply With Quote