-
stored procedure - please advise
for the past year i've ended all of my stored procedures that i write for the web with a "select @variable as foo into #sometablename" followed by "select foo from #sometablename" so that my web programmer can pull the resultset. in query analyzer i can use a print statement of the variable to test the resultset, however a simple print does not work for the web programmers. is there a better way to write/end my stored procedures???
see example below
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE [dbo].[sp_PHS_UPTIME_PERC]
@systemName varchar(50) = NULL AS
-- declare @systemName varchar(50)
-- set @systemName = 'phsaix004'
declare @uSCHdownTime numeric
declare @SCHdownTime numeric
declare @totalAvailMinutes numeric
declare @net numeric
set @uSCHdownTime = (
select Sum (datediff(mi, event_begin, event_end)) as foo
from uptimeevent
where system_name = @systemName and type = 1
group by system_name
)
set @SCHdownTime = (
select Sum (datediff(mi, event_begin, event_end)) as foo
from uptimeevent
where system_name = @systemName and type = 0
group by system_name
)
If (select datediff(mi, goLive, getdate()) as foo2 from uptimeSystem
where system_name = @systemName) > 525600
begin
set @totalAvailMinutes = 525600 - ISNULL(@SCHdownTime, 0)
end
else
begin
set @totalAvailMinutes = (
select datediff(mi, goLive, getdate()) as foo2 from uptimeSystem
where system_name = @systemName
) - ISNULL(@SCHdownTime, 0)
end
set @net =( @totalAvailMinutes - @uSCHdownTime )
--print @uSCHdownTime
-- drop table #temp
-- drop table #temp1
if @uSCHdownTime is null
begin
select 100 as foo into #temp
select foo from #temp
end
else
begin
select (@net / @totalAvailMinutes) * 100 as foo into #temp1
select foo from #temp1
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Your programmers should be able to read the result set. You can end the stored procedure with a return code of 0 for success and any other number for failure, and programmers can check that.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|