SQL2
1、移去空格
replace(@CertigierEmpId,' ','')
2、值对应转换显示
SELECT (case FlowStatus WHEN 1 then 4 WHEN 2 then 3 END ) AS FlowStatus FROM dbo.FlowExpense
3、表中插入一个字段
ALTER TABLE [dbo].[HR_ShiftConfig] ADD Duration DECIMAL(18,1) NULL
4.exists 比left join性能更优
SELECT * FROM dbo.OKR_ObjectEvaluateItem AS items WHERE CheckEmpId =0 and EXISTS( SELECT 1 FROM dbo.OKR_ObjectEvaluate WHERE EvaluateEndTime <GETDATE() AND ObjectStatus =3 AND items.MainID = ID)
5.像如下这样查值,如果没有找到,或输入-1
declare @FlowCheckStatus int set @FlowCheckStatus = -1 select @FlowCheckStatus = FlowCheckStatus from GB_FloCheckStepInforWip where id =-1 print @FlowCheckStatus
6.已知表名为字符串,查询表中数据。注意绿色处,带出值得方法必须重设置一个字符变量
declare @TableName nvarchar(200),@FlowID INT ,@FlowNo nvarchar(100),@FlowInitiateEmpID int
set @TableName = 'HR_FloLeaveOver'
SET @FlowID = 130
--exec ('select * from ' + @TableName + ' where ID = ' + ''+@FlowID+ '')
--带出值
declare @FlowIDStr nvarchar(200)
set @FlowIDStr = cast(@FlowID as nvarchar(200))
declare @SqlStr nvarchar(1000)
set @SqlStr = N'select top 1 @a = FlowNo,@b= FlowInitiateEmpID from '+ @TableName +' where ID = '+ @FlowIDStr
exec sp_executesql @SqlStr,N'@a nvarchar(100) output,@b int output',@FlowNo output,@FlowInitiateEmpID output
print @FlowNo
print @FlowInitiateEmpID
--用=‘’判断进不去的
if @FlowNo is null or @FlowInitiateEmpID is null
begin
print '为空!'
end