IF( SELECT count(*) FROM ['+ @shift_table +'] WHERE work_no=@work_no AND F_day=CAST(substring(@dateStr,7,2) AS INT))>0 @shift_table为一个表名参数。检查语法没有问题在调用存储过程时提示错误:对象名 ''+ @shift_table +'' 无效Exec update_SearchRecord '20060515','065'请帮忙看看,谢谢!
调试欢乐多
declare @sql nvarchar(4000)
declare @count int
select @sql='SELECT @count=count(*) FROM ['+ @shift_table +'] WHERE work_no='+@work_no+' AND F_day=CAST(substring('+@dateStr+',7,2) AS INT)'
exec sp_executesql @sql,N'@count int output',@count output
if @count>0
......
DECLARE @sql NVARCHAR(2000)
DECLARE @shift_table VARCHAR(100)
SET @shift_table='表名'
SET @sql=N'SELECT @c=count(*)
FROM ['+ @shift_table +']
WHERE work_no=@work_no
AND F_day=CAST(substring(@dateStr,7,2) AS INT)'
EXEC sp_executesql @sql,N'@c INT OUTPUT',@c OUTPUTIF @c>0
IF( SELECT count(*) FROM ['+ @shift_table +'] WHERE work_no=@work_no
AND F_day=CAST(substring(@dateStr,7,2) AS INT))>0
--该员工当天当班
BEGIN
SELECT @shift_id=F_shift FROM ['+ @shift_table +'] --取班次shiftid,065表作为员工-班次关联基准表,待调整
WHERE work_no=@work_no AND F_day=CAST(substring(@dateStr,7,2) AS INT)
IF (@shift_id is NULL or @shift_id=0)
BEGIN
GOTO nextPerson
END
SELECT @shift_st=f_ontime,@shift_et=f_offtime,@shift_istoday=f_istoToday FROM SHIFT WHERE shiftid=@shift_id
--PRINT '---shift_id:['+@shift_id+'] work_no:['+@work_no+'] '+' shift_st:['+datename(year,@shift_st)+']' ELSE
BEGIN
PRINT '---Work_no: ['+@work_no+']
SELECT @jrdk=0
END
IF( SELECT count(*) FROM ['+ @shift_table +'] WHERE work_no=@work_no AND F_day=CAST(substring(@dateStr,7,2) AS INT))>0 需要EXEC执行一下吗?我想根据查询结果再处理相应的代码。谢谢
INSERT @t1 SELECT 1 UNION SELECT 2
SELECT * FROM @t1DECLARE @t2 VARCHAR(100)
SET @t2='tb'
SELECT * FROM @t2 -- SELECT * FROM 'tb'
自己去想为什么错.
知道了。可。已经可以了。其实是把结果给一个变量,然后判断一样。谢谢!
数据类型 int 对于函数 substring 无效。允许的类型为: char/varchar、nchar/nvarchar 和 binary/varbinary。