create PROCEDURE [dbo].[Tradeinfo_Getinfo]
@username varchar(50)=null,
@active int=null,
@top int=10000
AS
declare @sqlwhere nvarchar(1000)
declare @sql nvarchar(1000)
set @sqlwhere=''
if(@username is not null)---@username
set @sqlwhere=@sqlwhere +' and username='+@username+''
if(@active is not null)---@active
set @sqlwhere=@sqlwhere+' and active='+str(@active)+''
set @sql='SELECT top '+str(@top)+' price,id FROM Tradeinfo where 1=1 '+@sqlwhere+' '
exec(@sql)
提示列名XXX无效。XXX是输入的username的值
------解决方案--------------------------------------------------------
- SQL code
if(@username is not null)---@username set @sqlwhere=@sqlwhere +' and username='''+@username+''''
------解决方案--------------------------------------------------------
if(@active is not null)---@active
set @sqlwhere=@sqlwhere+' and active='+str(@active)+''
这个没错是因为是数字,字符要加'
where name='非'
------解决方案--------------------------------------------------------
create PROCEDURE [dbo].[Tradeinfo_Getinfo]
@username varchar(50)=null,
@active int=null,
@top int=10000
AS
declare @sqlwhere nvarchar(1000)
declare @sql nvarchar(1000)
set @sqlwhere=''
if(@username is not null)---@username
set @sqlwhere=@sqlwhere +' and username='''+@username+''''
if(@active is not null)---@active
set @sqlwhere=@sqlwhere+' and active='+str(@active)+''
set @sql='SELECT top '+str(@top)+' price,id FROM Tradeinfo where 1=1 '+@sqlwhere+' '
exec(@sql)
------解决方案--------------------------------------------------------
- SQL code
create procedure dbo.tradeinfo_getinfo@username varchar(50)=null,@active int=null,@top int=10000asdeclare @sqlwhere nvarchar(1000);declare @sql nvarchar(1000);set @sqlwhere = '';if(@username is not null) set @sqlwhere=@sqlwhere + 'and [username]=@tusername ';if(@active is not null) set @sqlwhere=@sqlwhere + 'and [active]=@tactive ';set @sqlwhere=' where 1=1 '+@sqlwhere;set @sql = 'select top '+convert(nvarchar(15),@top)+ '[price],[id] from [tradeinfo]'+@sqlwheredeclare @param nvarchar(400)set @param='@tusername nvarchar(50),@tactive int';execute sp_executesql @sql,@param,@tusername=@username,@tactive=@active;
------解决方案--------------------------------------------------------
字符串缺少单引号