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.
Author |
Topic |
Emile
Starting Member
5 Posts |
Posted - 2012-10-23 : 07:07:02
|
hello?I'm very new to T-SQL, I work part time as a programmer.I have difficulty in adapting a stored proc to recieve a parameter from an exact replicant of itself.This is the initial stored proc : alter procedure mlmMeterUsageStats @Action int=0asbegin set NOCOUNT on declare @Tot int, @Date datetime set @Date = substring(convert(varchar(10),getdate(),111),1,8) + '01' select @Tot = count(*) from mlmMeter where TypeId = 0 and Disabled = 0 select CalcDate, left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2) as Descr, sum(case when IsEstimate=0 then 1 else 0 end) as Actual, sum(case when IsEstimate=1 and NoteCode >= 0 then 1 else 0 end) as Est, sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end) as Calc, @Tot-count(*) as NoRead, convert(varchar(10),getdate(),111) as TheDate, @Tot as Tot, '<tr><td><b>' + left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2) + '</td><td bgcolor="green" colspan="' + cast(sum(case when IsEstimate=0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="red" colspan="' + cast(sum(case when IsEstimate=1 and NoteCode >= 0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="yellow" colspan="' + cast(sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot as varchar(10)) + '"><br></td><td bgcolor="black" colspan="' + cast(100 - (sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot) - (sum(case when IsEstimate=0 then 1 else 0 end)*100/@Tot) - (sum(case when IsEstimate=1 and NoteCode < 0 then 1 else 0 end)*100/@Tot) as varchar(10)) + '"><br></td></tr>' as HTML from mlmMeter M join mlmMeterUsage U on M.MeterId = U.MeterId and M.Disabled = 0 and M.TypeId = 0 and U.CalcDate >= dateadd(month,case when @Action = 0 then -11 else -2 end,@Date) group by CalcDate, left(datename(month,CalcDate),3) + ' ' + right(cast(year(CalcDate) as varchar(10)),2) order by CalcDate desc return 0endThen I need to create a proc that will recieve the CalcDate |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-23 : 07:23:53
|
What do you want your new stored procedure do? Should it do exactly the same thing as the current stored procedure, except it should accept a date? Or are you trying to change the behavior of the stored procedure? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-23 : 18:38:29
|
hmm...what does this mean?I have difficulty in adapting a stored proc to recieve a parameter from an exact replicant of itself.why do you need same procedure twice?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|