日期:2014-05-18  浏览次数:20590 次

SqlServer如何让字段名是动态的?
CREATE   PROCEDURE   OrderSearch
(

@first   Datetime,
@last   Datetime,
@orderstatu   varchar(50),
@fieldvalue     varchar(50),
@field,varchar(1000)
)
as
begin
declare   @sql   nvarchar(2000)
declare   @fiel   nvarchar(100)
set   @fiel= '@field '
set   @sql= 'select   a.orderid,a.orderdate,b.adjustedprice,b.sendmoney,b.sendmode,b.paymode,a.username,a.telephone,d.description   from   orders   as   a    
inner   join   orderdetail   as   b   on   a.orderid=b.orderid   inner   join   orderstatuschanges   c   on   a.orderid=c.orderid   inner   join   orderstatuscodes   as   d   on   c.statuscode=d.statuscode  
where   (a.orderdate> "+   @first   + ")   and   a.orderdate < "+   @last   + "   and   ',   '   +   @fiel   +   ', '= '   +   @fieldvalue   +   '   and   (d.description= '   +   @orderstatu   +   ') '
exec   sp_executesql   @sql
end
一直有语法错误,望各位大侠给予解决!

------解决方案--------------------
--try

CREATE PROCEDURE OrderSearch
(

@first Datetime,
@last Datetime,
@orderstatu varchar(50),
@fieldvalue varchar(50),
@field varchar(1000)
)
as
begin
declare @sql nvarchar(2000)
declare @fiel nvarchar(100)
set @fiel=@field --沒用
set @sql= 'select a.orderid,a.orderdate,b.adjustedprice,b.sendmoney,b.sendmode,b.paymode,a.username,a.telephone,d.description from orders as a
inner join orderdetail as b on a.orderid=b.orderid inner join orderstatuschanges c on a.orderid=c.orderid inner join orderstatuscodes as d on c.statuscode=d.statuscode
where (a.orderdate> ' ' '+ @first + ' ' ') and a.orderdate < ' ' '+ @last + ' ' ' and ' + @field + '= ' ' ' + @fieldvalue + ' ' ' and (d.description= ' ' ' + @orderstatu + ' ' ') '
exec sp_executesql @sql
end