是否可以使用T-sql执行存储在表中的sql语句?
DECLARE @Query text SET @Query = (Select Query FROM SCM.dbo.CustomQuery)
存储在表中的语句是ad-hoc语句,可以是SELECT AT 100 * FROM ATable到更复杂的语句:
Select J.JobName As Job,JD.JobDetailJobStart AS StartDate,JD.JobDetailJobEnd AS EndDate,( SELECT (DATEDIFF(dd,JD.JobDetailJobStart,JD.JobDetailJobEnd) + 1) -(DATEDIFF(wk,JD.JobDetailJobEnd) * 2) -(CASE WHEN DATENAME(dw,JD.JobDetailJobStart) = 'Sunday' THEN -1 ELSE 0 END) -(CASE WHEN DATENAME(dw,JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END) ) AS NumberOfWorkingDays,JD.JobDetailDailyTarget AS DailyTarget,JD.JobDetailWeeklyTarget AS WeeklyTarget,JD.JobDetailrequiredQTY AS requiredQuantity,( Select SUM(sJL.JobLabourQuantityEmployees) From JobLabour sJL ) AS NumberOfEmployees,( Select SUM((sEM.EmployeeDesignationDefaultRate * sJL.JobLabourQuantityEmployees)*8)*(SELECT (DATEDIFF(dd,JD.JobDetailJobEnd) = 'Saturday' THEN -1 ELSE 0 END)) from EmployeeDesignation sEM Inner join JobLabour sJL on sJL.EmployeeDesignationID = sEM.EmployeeDesignationID ) AS FullEmployeeRate from Job J Inner Join JobDetail JD on JD.JobID = J.JobID Inner Join JobLabour JL on JL.JobID = J.JobID WHERE J.JobActive = 0
解决方法
您可以使用
EXECUTE sp_executesql @Query
运行你的T-sql
http://msdn.microsoft.com/en-us/library/ms188001%28v=sql.90%29.aspx