存储过程中拼接sql并且参数化

ALTER PROCEDURE [dbo].[proc_test]
(
   @orderby nvarchar(100) = ' order by id desc  ',
   @userid int,
   @stime datetime,
   @etime datetime
)
AS
BEGIN
   DECLARE @strWhere nvarchar(1000)='';         --where条件
   DECLARE @execsql nvarchar(1000);        -- 主语句
   DECLARE @param nvarchar(1000);        -- 参数
    
   -------拼接where条件---------------------------------------------------------------------
   if(@userid>0)
   begin
        SET @strWhere += ' and [userid] = @userid '
   end
   --'1753/1/1 0:00:00'为时间传过来的默认值，表示无此筛选条件
   if(@stime <> '' and @stime>'1753/1/1 0:00:00')
   begin
        SET @strWhere += ' and [time] >= @stime '
   end
   --'1753/1/1 0:00:00'为时间传过来的默认值，表示无此筛选条件
  if(@etime <> '' and @etime>'1753/1/1 0:00:00')
   begin
        SET @strWhere += ' and [time] <= @etime '
   end 
   ------拼接where条件 end---------------------------------------------------------------------
   
   --查询sql
   set  @execsql = ' SELECT TOP 1000 [id] ,[time] FROM [tradeinfo]  WHERE 1 = 1 '
                    + @strWhere +@orderby;
    
        
    --参数化处理
    set @param =N'@userid int,@stime datetime,@etime datetime';
    EXEC sys.sp_executesql @execsql ,@param,
          @userid=@userid,
          @stime =@stime,
          @etime =@etime
          
END

 

c#调用如下：

SqlParameter[] parameters =
{
     new SqlParameter("@orderby", SqlDbType.VarChar, 30),
     new SqlParameter("@userid", SqlDbType.Int,4),
     new SqlParameter("@stime", SqlDbType.DateTime,9),
     new SqlParameter("@etime", SqlDbType.DateTime,9)
};

parameters[0].Value = " order by id desc ";
parameters[1].Value = 0;
parameters[2].Value = SqlDateTime.MinValue.Value;//默认最小值：1753/1/1 0:00:00
parameters[3].Value = SqlDateTime.MinValue.Value;//默认最小值：1753/1/1 0:00:00

var ds = ExecuteNonQuery(CommandType.StoredProcedure, "proc_test", parameters);

 

上面的存储过程做了参数化处理，可以避免sql注入，相比直接拼接(SET @strWhere += ' and [userid] = ' +convert(varchar,@userid),然后用EXEC()方法执行)，更高效、更安全，当然维护起来有点麻烦，还有一点排序的参数@orderby好像没法参数化，以后有更好的方法再更新此文。