June 16, 2010 at 5:45 am
Hi All,
alter PROCEDURE MonthlyDepreciation
(
@Classification int
)
AS
BEGIN
Declare @Query varchar(1000)
set @Query= @Query + 'select assetname,companyname,acquisationdate,currentamount,age,monthlydepreciation,yearlydepreciation,
monthlydepreciation*age as accumulated, currentamount-(monthlydepreciation*age) as NetBookvalue
from(
select assetname,companyname,acquisationdate, currentamount, datediff("M",acquisationdate,getdate()) as Age,
acquisationamount/lifetime as MonthlyDepreciation, currentamount/lifetime*12 YearlyDepreciation
from registration where 1=1 '
if @Classification <=0
begin
set @Query = @Query + ' and ClassificationID = '+@Classification +''
end
set @Query = @Query + ')MonthDepreciation'
exec (@Query)
END
GO
the above stored procedure is not returning me any rows when i execute it like EXEC MonthlyDepreciation @Classification =0
any help is highly appreciated
June 16, 2010 at 5:55 am
Are there any rows in the table with a classification of 0?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 16, 2010 at 5:58 am
yes it should return all the rows. by the way i am executing it in sql server management studio.
June 16, 2010 at 6:08 am
Please post table definitions and sample data. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
What's the dynamic SQL string that gets executed? Use Print to print it out.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply