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
