1.在C#中如果对参数赋值为null,总会提示"存储过程XXX需要参数XXX,但未能提供"之类的信息,不能赋空参数,不得不把原来的if XXX is null改为if XXX=''.
2.存储过程运行时生成语句必须使用存储过程sp_executesql,使用方法可见代码.
3.直接在存储过程里使用模糊查询无法实现,也就是说在里面使用and XXX like %@XXX%会报错,如果有高人知道解决办法麻烦告诉我,只能在参数里赋类似%XXX%的参数.
4.存储过程中的参数如果有bit值的,在使用c#赋值时,应赋int值.
create procedure UP_lvSalClient_GetList
![](/Images/OutliningIndicators/None.gif)
(
![](/Images/OutliningIndicators/None.gif)
@ClientID bigint,@Saler nvarchar(50),@Name nvarchar(50),@Area nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@City nvarchar(50),@Company nvarchar(100),@Sort1 nvarchar(50),@Sort2 nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@Sort3 nvarchar(50),@Brand nvarchar(50),@Address nvarchar(50),@Source nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@LikeMe nvarchar(50),@valid bit
![](/Images/OutliningIndicators/None.gif)
)
![](/Images/OutliningIndicators/None.gif)
AS
![](/Images/OutliningIndicators/None.gif)
declare @SQLStr nvarchar(200)
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=N'select * from lvSalClient where valid=@valid'
![](/Images/OutliningIndicators/None.gif)
if @ClientID<>0
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and ClientID=@ClientID'
![](/Images/OutliningIndicators/None.gif)
if @Saler<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Saler=@Saler'
![](/Images/OutliningIndicators/None.gif)
if @Name<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and name like @Name'
![](/Images/OutliningIndicators/None.gif)
if @Area<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Area=@Area'
![](/Images/OutliningIndicators/None.gif)
if @City<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and City like @City'
![](/Images/OutliningIndicators/None.gif)
if @Company<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Company like @company'
![](/Images/OutliningIndicators/None.gif)
if @Sort1<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Sort1=@Sort1'
![](/Images/OutliningIndicators/None.gif)
if @Sort2<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Sort2=@Sort2'
![](/Images/OutliningIndicators/None.gif)
if @Sort3<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Sort3=@Sort3'
![](/Images/OutliningIndicators/None.gif)
if @Brand<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Brand=@Brand'
![](/Images/OutliningIndicators/None.gif)
if @Address<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and Address like @address'
![](/Images/OutliningIndicators/None.gif)
if @source<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and source like @source'
![](/Images/OutliningIndicators/None.gif)
if @likeme<>''
![](/Images/OutliningIndicators/None.gif)
set @SQLStr=@SQLStr+N' and likeme like @likeme'
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
exec sp_executesql @SQLStr,
![](/Images/OutliningIndicators/None.gif)
N'@ClientID bigint,@Saler nvarchar(50),@Name nvarchar(50),@Area nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@City nvarchar(50),@Company nvarchar(100),@Sort1 nvarchar(50),@Sort2 nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@Sort3 nvarchar(50),@Brand nvarchar(50),@Address nvarchar(50),@Source nvarchar(50),
![](/Images/OutliningIndicators/None.gif)
@LikeMe nvarchar(50),@valid bit',
![](/Images/OutliningIndicators/None.gif)
@ClientID,@Saler,@Name,@Area,
![](/Images/OutliningIndicators/None.gif)
@City,@Company,@Sort1,@Sort2,
![](/Images/OutliningIndicators/None.gif)
@Sort3,@Brand,@Address,@Source,
![](/Images/OutliningIndicators/None.gif)
@LikeMe,@valid
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)