ALTER proc [dbo].[proc_GetPrjWhere]
@Dptid int,
@Userid int,
@PrjCode varchar(20),
@BCreatdate datetime,
@ECreatdate datetime
as
declare @where varchar(200)
set @where = '1=1'
exec('select * from Info_prjvalues where ='+@where+'')
执行后说 “=”号附近有语法错误
@Dptid int,
@Userid int,
@PrjCode varchar(20),
@BCreatdate datetime,
@ECreatdate datetime
as
declare @where varchar(200)
set @where = '1=1'
exec('select * from Info_prjvalues where ='+@where+'')
执行后说 “=”号附近有语法错误
解决方案 »
- SQL查询某时间之前的最新记录
- 【【SqlServer2000,怎么对字段内的内容排序?】】
- 急.急.急.在線等:一個分組循環語句
- Insert的值是select来的,补充SQL语句
- 大家好,我用企业管理器添加数据时,总提示"键列信息不足或不正确,更新影响到多行!"为什么?
- 为什么我的数据库的未用的空间量那么大?
- 关于读取最新记录的问题
- 不知道怎么描述,大家帮我看看吧
- 前不久,买了本SQL SERVER 2000,现在正在看,我怎么找不到SQL Server 2000 提供的Northwind里的Accounts,书上的实例就是用这个表的,好急
- 如何给 SQL Server的事务日志减肥?
- 散分有图,回答正确再加40分,急在线等
- 国内某知名互联网企业的一道压轴笔试题
declare @where varchar(200)
set @where = '1=1'
DECLARE @sql VARCHAR(500)
SET @sql='select * from Info_prjvalues where ='+@where+''
SELECT @sql
/*
select * from Info_prjvalues where =1=1
*/两个等号,所以不对
ALTER PROC [dbo].[proc_GetPrjWhere]
@Dptid INT ,
@Userid INT ,
@PrjCode VARCHAR(20) ,
@BCreatdate DATETIME ,
@ECreatdate DATETIME
AS
DECLARE @where VARCHAR(200)
SET @where = '1=1'
EXEC('select * from Info_prjvalues where '+@where)--''是空字符串加上也没有用
这个可用IF ... ELSE ...循环可用while
@Dptid int,
@Userid int,
@PrjCode varchar(20),
@BCreatdate datetime,
@ECreatdate datetime
as
declare @where varchar(200)
set @where = '1=1'
if(@Dptid is not null)
begin
set @where = 'Dptid ='+@Dptid
end
exec('select * from Info_prjvalues where '+@where+'')执行后Dptid 转换成int型失败。 为什么??
ALTER PROC [dbo].[proc_GetPrjWhere]
@Dptid INT = NULLL ,
@Userid INT = NULLL ,
@PrjCode VARCHAR(20) = NULLL ,
@BCreatdate DATETIME = NULLL ,
@ECreatdate DATETIME = NULLL
AS
--第一种方式,设置默认值为null 用isnull 判断,当没有这个参数时,为null
SELECT *
FROM Info_prjvalues
WHERE Dptid = ISNULL(@Dptid, Dptid)
AND Userid = ISNULL(@Userid, Userid)
@Dptid int,
@Userid int,
@PrjCode varchar(20),
@BCreatdate datetime,
@ECreatdate datetime
as
declare @where varchar(200)
set @where = '1=1'
if(@Dptid is not null)
begin
set @where = 'Dptid ='+ltrim(@Dptid)end
exec('select * from Info_prjvalues where '+@where+'')
ALTER PROC [dbo].[proc_GetPrjWhere]
@Dptid INT = NULLL ,
@Userid INT = NULLL ,
@PrjCode VARCHAR(20) = NULLL ,
@BCreatdate DATETIME = NULLL ,
@ECreatdate DATETIME = NULLL
AS
--第二种方式,就是用if判断了
DECLARE @where VARCHAR(8000)
SET @where = ''
IF ( @Dptid IS NOT NULL )
SET @where = @where + 'and Dptid=' + LTRIM(@Dptid) + ' '
IF ( @Userid IS NOT NULL )
SET @where = @where + 'and Userid=' + LTRIM(@Userid) + ' '
EXEC('select * from Info_prjvalues where 1=1 '+@where)
@Dptid int = null,
@Userid int = null,
@PrjCode varchar(20) = null,
@BCreatdate datetime = null,
@ECreatdate datetime = null
as
declare @where varchar(200)
set @where = '1=1'
if(@Dptid is not null and @Userid is null)
begin
set @where = 'Dptid ='+LTRIM(@Dptid)
end
if(@Userid is not null and @Dptid is not null)
begin
set @where ='Userid ='+LTRIM(@Userid)+' and Dptid ='+LTRIM(@Dptid)
end
if(@PrjCode is not null)
begin
set @where ='PrjCode = '+@PrjCode
end
exec('select * from Info_prjvalues where '+@where+'')消息 207,级别 16,状态 1,第 1 行
列名 '有间客栈' 无效。(1 行受影响)