asp.net|server|架构|数据存储过程：

/**********************************

功能：根据一定条件读取功能记录

作者：Rexsp

创建日期：2004-01-13

修改者：

修改日期：

**********************************/

ALTER PROCEDURE GetScoreSetting

(

ScoreSettingID INT=-1, ---设置ID

FunctionID INT=-1, ---功能ID

OperationID INT=-1, ---操作ID

RoleTypeID INT=-1, ---角色类型

BBSTypeID INT=-1, ---版块类型

Score INT=-1, ---积分设置

BB INT=-1, ---币币设置

BuyType INT=-1, ---购买类型 0:不是购买类型 1:一次性购买 2:反复购买

FunctionState INT=-1 ---功能状态

)

AS

SET NOCOUNT ON

DECLARE strSQL NVARCHAR(1000)

SET strSQL = 'SELECT * FROM [ScoreSetting] WHERE ckScoreSettingID = ckScoreSettingID'

--- Add KeyWords Begin ---

IF ScoreSettingID<> -1

BEGIN

SET strSQL = strSQL + ' AND ScoreSettingID= ckScoreSettingID'

END

IF FunctionID<> -1

BEGIN

SET strSQL = strSQL + ' AND FunctionID= ckFunctionID'

END

IF OperationID<>-1

BEGIN

SET strSQL = strSQL + ' AND OperationID = ckOperationID'

END

IF RoleTypeID<>-1

BEGIN

SET strSQL = strSQL + ' AND RoleTypeID = ckRoleTypeID'

END

IF BBSTypeID<>-1

BEGIN

SET strSQL = strSQL + ' AND BBSTypeID = ckBBSTypeID'

END

IF Score<>-1

BEGIN

SET strSQL = strSQL + ' AND Score = ckScore'

END

IF BB<>-1

BEGIN

SET strSQL = strSQL + ' AND BB= ckBB'

END

IF BuyType<>-1

BEGIN

SET strSQL = strSQL + ' AND BuyType= ckBuyType'

END

IF FunctionState<>-1

BEGIN

SET strSQL = strSQL + ' AND FunctionState= ckFunctionState'

END

--- Add Where Key Word ---

--- Run SQL Begin ---

EXECUTE sp_executesql strSQL,

N' ckScoreSettingID INT,

ckFunctionID INT,

ckOperationID INT,

ckRoleTypeID INT,

ckBBSTypeID INT,

ckScore INT,

ckBB INT,

ckBuyType INT,

ckFunctionState INT',

ckScoreSettingID=ScoreSettingID,

ckFunctionID=FunctionID,

ckOperationID = OperationID,

ckRoleTypeID = RoleTypeID,

ckBBSTypeID = BBSTypeID,

ckScore = Score,

ckBB = BB,

ckBuyType = BuyType,

ckFunctionState = FunctionState

--- Run SQL End ---



一点说明：

此存储过程会根据数据层的类有没有传递相应的参数值进来而动态创建查询语句，然后用系统自带的存储过程执行ｓｑｌ语句，用系统存储过程执行ｓｑｌ语句的好处是可以自动转义字符。而动态创建查询语句的好处，就非常大了，这会省下我们写很多种条件判断，尤其是对那些字段比较多的表来讲，一个排列组合下来的情况太多了，而利用存储过程动态创建ｓｑｌ语句所作的判断数和字段数基本上是一致的，这里会给入参赋初始值，如果不等于初始值就说明数据层类有传递参数进来，这样就加上相应条件字符的查询条件。