How to add results of two sql 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

How to add results of two sql statement

Author

Topic

atulbharadwaj
Starting Member

11 Posts

Posted - 2009-12-14 : 23:49:12

hi,
i m new in sql server, i have created a procedure to find the result of two sql state and need to calculate them but i m not getting success to do this, can some one help me.
its not necessary that u give me solution on my examaple, if you want can give me another example
i m sending code here

Create PROCEDURE [dbo].[WeightageInASI]
@Quarter varchar(10),
@Sector varchar(100),
@CurrentYear varchar(10)

AS
BEGIN
SET NOCOUNT ON;
declare @Weightage float
declare @Weightage1 varchar(200)
declare @Weightage2 varchar(200)
declare @Weightagevalue float

select @Weightage1 = '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) +''
--select @Weightage
exec(@Weightage1)

select @Weightage2 = '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) +''
--select exec(@Weightage1,exec(@Weightage2)
exec weigth @Weightage1
----
select @Weightage = ((@Weightage1) * 100 / @Weightage2)
--select @Weightage

end

thanks

atul

sathiesh2005
Yak Posting Veteran

85 Posts

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

You have used the variables @Weightage1 and @Weightage2 with the data type varchar. You should change it to numeric/float type.
OR
you can convert them to numeric at the time of calculation.
for example,
select @Weightage = ((convert(Numeric,@Weightage1)) * 100 / (convert(Numeric,@Weightage2))

Regards,
Sathieshkumar. R

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources