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

SqlParameter[] parameters =

{

     new SqlParameter("@orderby", SqlDbType.VarChar, ),

     new SqlParameter("@userid", SqlDbType.Int,),

     new SqlParameter("@stime", SqlDbType.DateTime,),

     new SqlParameter("@etime", SqlDbType.DateTime,)

};

parameters[].Value = " order by id desc ";

parameters[].Value = ;

parameters[].Value = SqlDateTime.MinValue.Value;//默认最小值：1753/1/1 0:00:00

parameters[].Value = SqlDateTime.MinValue.Value;//默认最小值：1753/1/1 0:00:00

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