存储过程例子

USE [LONGTANTEST]

GO

/****** Object:  StoredProcedure [dbo].[USP_CheckModuleGrade]    Script Date: 2014-03-25 11:15:12 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Frank.Lu

-- Create date: 2014-03-21

-- Description: 判断模块版本运行权限

-- =============================================

CREATE PROCEDURE [dbo].[USP_CheckModuleGrade] 

-- Add the parameters for the stored procedure here

@moduleName varchar(50)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here

declare @checkFlag varchar(128)

set @checkFlag = (select SETUPVALUE from FOption where SETUPROOM='SYSTEM' and SETUPSECT='SYSTEM_GRADE_CHECK' and SETUPUSER='SYSTEM')

if (@checkFlag = '1')

begin

declare @moduleGrade int, @hosGrade int

set @moduleGrade = (select ModuleGrade from ModuleGrade where FileName = @moduleName)

set @hosGrade = (select top 1 SPARE09 from HosInfo)

if (@moduleGrade > @hosGrade)

begin

return 0

end

else

begin

return 1

end

end

else

begin

return 1

end

END

GO