need the values return through the two select stat

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

need the values return through the two select stat

Author

Topic

atulbharadwaj
Starting Member

11 Posts

Posted - 2009-12-15 : 13:42:58

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-16 : 08:07:32

Looks like a big mess to me. Why are you creating your queries using strings rather than plain sql?

I would take a step back and look at the overall approach you're taking.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 08:37:05

You are passing column name as parameters so dynamic sql is the way to go
Have a look at sp_executesql in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-16 : 08:39:30

Also too many cross-posts
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137226
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=137172

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources