Sql字符串拼接

存储过程实现多条件查询

1 select * from table where 
2 (Column1 = @Column1 or @Column1 is null) 
3 and (Column2 = @Column2 or @Column2= '')


 

更新表，只修改传进来的参数，为“”的参数不进行修改。

 1 ALTER proc UpdateNoImg
 2 @IsHotCoupon int,
 3 @CouponName nvarchar(100),
 4 @CouponPic varchar(100),
 5 @ThumbnailImg varchar(50),
 6 @BuyValueMin decimal(18,0),
 7 
 8 @CouponValue decimal(18,0),
 9 @BrandID int,
10 @Type int,
11 @Description nvarchar(max),
12 @CouponID int
13 as
14 begin
15 
16     declare @sqlstr  varchar(5000)
17     declare @sqlstr1 varchar(2000)
18     declare @sqlstr2 varchar(1000)
19     declare @sqlstr3 varchar(1000)
20     declare @sqlstr4 varchar(1000)
21         
22     set @sqlstr1=' update  Coupon set IsHotCoupon = '+  convert(nvarchar(20),@IsHotCoupon) +'  
23                     ,CouponName = '''+ @CouponName + ''' 
24                     ,BuyValueMin='+  convert(nvarchar(20),@BuyValueMin) + '
25                     ,CouponValue='+ convert(nvarchar(20),@CouponValue) + ' 
26                     ,Type=' + convert(nvarchar(20),@Type) + ' 
27                       ,BrandID= ' + convert(nvarchar(20),@BrandID) + 
28                       ',Description= ''' + @Description  + '''  '                 
29      
30      set  @sqlstr2=',CouponPic='+''''+ @CouponPic +''''    
31      set  @sqlstr3=    ',ThumbnailImg='+''''+ @ThumbnailImg +''''        
32      set  @sqlstr4='  WHERE CouponID ='+ convert(nvarchar(20),@CouponID)+''           
33         
34         if @CouponPic=''
35         begin
36                 set @sqlstr2=''
37         end
38         if @ThumbnailImg=''
39         begin
40     
41                 set @sqlstr3=''
42         end
43         set @sqlstr=@sqlstr1+@sqlstr2+@sqlstr3+@sqlstr4
44     
45     
46     
47         execute  (@sqlstr)--这句一定要括号 经常忘记
48     
49 end    
50 
51 
52

 

posted @ 2013-06-21 16:09  老树昏鸦  阅读(858)  评论(0)  编辑  收藏  举报