Alter proc selectproduct
@ Prodid varchar (10 ),
@ Prodname nvarchar (30 ),
@ Categoryid varchar (5 ),
@ Minprice decimal (10, 2 ),
@ Maxprice decimal (10, 2)
As
Declare @ SQL varchar (2000)
Set @ SQL = 'select * from product where 1 = 1'
If @ prodid <>''
Set @ SQL = @ SQL + 'and prodid like ''' %' + @ prodid + '% '''
If @ prodname <>''
Set @ SQL = @ SQL + 'and prodname like ''' %' + @ prodname + '% '''
If @ categoryid <> 'all'
Set @ SQL = @ SQL + 'and categoryid = ''' + @ categoryid + ''''
If @ minprice <>-1
Set @ SQL = @ SQL + 'and price> =' + Cast (@ minprice as varchar)
If @ maxprice <>-1
Set @ SQL = @ SQL + 'and price <=' + Cast (@ maxprice as varchar)
Set @ SQL = @ SQL + 'order by addtime DESC'
-- Print @ SQL
Exec (@ SQL)
Use single quotes to connect strings. The original field value is changed to double single quotes by single quotes.
