如何把这条SQL语句写成存储过程？

[zzxap]

			StringBuilder   sql2   =   new StringBuilder( "Select Top " + PageSize + " * from I_ToolPlan where WTPROD+WTTOOL not in(select top " 
				+ PageSize * CurrentPage  + " WTPROD+WTTOOL from I_ToolPlan order by WTPROD+WTTOOL desc) "); 



      
			if(TextBox1.Text != "" & TextBox2.Text != "" )   

			{ 
				sql2.Append( " and WTPROD between '"+TextBox1.Text.Trim()+"' and '"+TextBox2.Text.Trim()+"'  ");
		
			} 

			if(TextBox3.Text != "" & TextBox4.Text != "")   

			{
				sql2.Append(" and WTTOOL >='"+TextBox2.Text.Trim()+"'  and  WTTOOL<= '"+TextBox4.Text.Trim()+"'");
			}
			

			
				sql2.Append(" order by WTPROD+WTTOOL desc");

[pt1314917]

create proc p_wsp
@textbox1 varchar(50)='',
@textbox2 varchar(50)='',
@textbox3 varchar(50)='',
@textbox4 varchar(50)='',
@pagesize int,
@CurrentPage int
as
	declare @sql varchar(4000)
	declare @where varchar(1000)
	set @where = ''
	if @textbox1!='' and @textbox2!=''
		set @where = @where +' and WTPROD between '''+@textbox1+''' and '''+@textbox2+''''	
	if @textbox3!='' and @textbox4!=''
		set @where = @where +' and WTTOOL between '''+@textbox3+''' and '''+@textbox4+''''

	set @sql='Select Top ' + ltrim(@PageSize) + ' * from I_ToolPlan where WTPROD+WTTOOL not in
	(select top '+ ltrim(@PageSize * (@CurrentPage-1))  + ' WTPROD+WTTOOL from I_ToolPlan order by WTPROD+WTTOOL desc)'
	+@where +' order by WTPROD+WTTOOL desc'
        exec(@sql)

[zzxap]

可以這樣直接調用這個存儲過程嗎？
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = sql1;
cmd.Parameters.Add(WRLOC);
cmd.Parameters.Add(WRFAC);
cmd.Parameters.Add(WRPROD);
cmd.Parameters.Add(WRTOOL);
cmd.Parameters.Add(WRRDTE);
SqlDataAdapter ada = new SqlDataAdapter(cmd);

[hery2002]

首先直接相加 WTPROD+WTTOOL 这个会潜在的问题,比如说1 ,111 和 11,11这样的情况,
其实,修改为存储过程格式如下:
随手写的,自己改改

create proc p_test
@pWTPROD nvarchar(20),
@pWTTOOL nvarchar(20),
@pWTPROD2 nvarchar(20),
@pWTTOOL2 nvarchar(20),
@pPageSize int,
@pCurrentPage  int
as
begin
   declare @sql nvarchar(4000),@sqlWhere nvarchar(2000)
   select @sql = 'Select Top (' + ltrim(@pPageSize) + ') * from I_ToolPlan where WTPROD+''&%$#@''+WTTOOL not in(select top ('+ ltrim(@pPageSize * @pCurrentPage)  + ') WTPROD+''&%$#@''+WTTOOL from I_ToolPlan order by WTPROD+''&%$#@''+WTTOOL desc)',@sqlWhere =''
   if (( @pWTPROD !='') and (@pWTPROD is not null) and ( @pWTPROD2 !='') and (@pWTPROD2 is not null)) 
     set @sqlWhere  = @sqlWhere + ' AND WTPROD between '''+ltrim(@pWTPROD) + ''' and ''' +ltrim(@pWTPROD2)+''''
   if (( @pWTTOOL !='') and (@pWTTOOL is not null) and ( @pWTTOOL2 !='') and ( @pWTTOOL2 is not null)) 
     set @sqlWhere  = @sqlWhere + ' AND WTTOOL between '''+ltrim(@pWTTOOL) + ''' and ''' +ltrim(@pWTTOOL2)+''''
  
   if ( @sqlWhere is not null ) set @sqlWhere  = ' WHERE 1=1 ' + @sqlWhere  
   
   set @sql = @sql + @sqlWhere  + ' order by WTPROD+''&%$#@''+WTTOOL desc'
    
end

[arronlsw]

看不懂