July 20, 2009 at 2:00 pm
Hi, I'm new to dynamic SQL and I avoid it as much as possible. The code below is parsed and executed in SQL SMS 2005 without issues; but when I run a "select * from [dbo].[FN_EDS_TIME_DSHIFT]() " it shows the error
'The name 'Insert into @MyTemp... AND InOut ='OUT' GROUP BY strEmpID, DepCode' is not a valid identifier.
Can someone assist me on what it means? Thank you.
EDS_TD (table source sample data)
strEmpID TD DepCode
00000001,2009-07-18 22:02:42.000,2nd Grp Split 4am
00000001,2009-07-19 06:16:01.000,2nd Grp Split 4am
00000002,2009-07-19 05:49:46.000,2nd Grp Split 4am
---------------------------
USE [SomeDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[FN_EDS_TIME_DSHIFT]()
RETURNS @MyTemp TABLE
(
strEmpID CHAR(8) NOT NULL,
Times DATETIME NOT NULL,
Shift VARCHAR(30) NOT NULL
)
AS
BEGIN
DECLARE @vsql VARCHAR(2000)
DECLARE @vShiftSet VARCHAR(500)
DECLARE @WK INT
DECLARE @bIsEven BIT
SELECT @WK = DATEPART(WEEK,MIN(TD)) FROM EDS_TD GROUP BY strEmpID
SET @bIsEven = CASE WHEN (@WK % 2)=0 THEN 1 ELSE 0 END
SET @vShiftSet =
CASE WHEN (@bIsEven=1)
THEN '1st Grp Rep'
ELSE '''2nd Grp Rep''' + ',' + '''2nd Grp Split 2AM''' + ',' +
'''2nd Grp Split 1AM''' + ',' + '''2nd Grp Split 3AM''' +
',' + '''2nd Grp Split 4AM'''
END
SET @vsql = 'SELECT TOP 100 PERCENT T.* FROM ( '
SET @vsql = @vsql + 'SELECT strEmpID, MIN(TD) as Times, DepCode as [Shift] '
SET @vsql = @vsql + 'FROM EDS_TD '
SET @vsql = @vsql + 'WHERE DATEPART(dd,TD) IN ( '
SET @vsql = @vsql + ' SELECT DATEPART(day,MIN(TD)) FROM EDS_TD) '
SET @vsql = @vsql + ' AND DATEPART(hh,TD) BETWEEN 19 AND 24 '
SET @vsql = @vsql + ' AND InOut =''IN'' '
SET @vsql = @vsql + 'GROUP BY strEmpID, '
SET @vsql = @vsql + 'DepCode '
SET @vsql = @vsql + 'UNION ALL '
SET @vsql = @vsql + 'SELECT strEmpID, MAX(TD) as Times, DepCode as [Shift] '
SET @vsql = @vsql + 'FROM EDS_TD '
SET @vsql = @vsql + 'WHERE DATEPART(dd,TD) IN ( '
SET @vsql = @vsql + ' SELECT DATEPART(day,DateADD(day,1,MIN(TD))) FROM EDS_TD) '
SET @vsql = @vsql + ' AND DATEPART(hh,TD) BETWEEN 0 AND 10 '
SET @vsql = @vsql + ' AND InOut =''OUT'' '
SET @vsql = @vsql + 'GROUP BY strEmpID, '
SET @vsql = @vsql + 'DepCode) AS T '
SET @vsql = @vsql + 'WHERE T.strEmpID IN (SELECT DISTINCT strEmpID FROM EDS_TD '
SET @vsql = @vsql + 'WHERE DepCode In (' + @vShiftSet + ')) '
SET @vsql = @vsql + 'ORDER BY T.strEmpID ASC '
SET @vsql = 'INSERT INTO @MyTemp ' + @vsql
EXEC @vsql
RETURN
END
July 20, 2009 at 2:41 pm
Hey, by the way, I don't see why you use Dynamic SQL for that operation, all you do is a select/case statement, you should be able to include this in a normal sql query. Avoid Dynamic SQL at all costs, It's really hard to maintain, and can cause performance issues.
Cheers,
J-F
July 20, 2009 at 2:45 pm
You're not going to be able to use a function for this.
Functions:
- cannot run EXEC statements
- cannot run stored procedures unless they're system extended stored procs (which SP_executeSQL is not)
You might get a little further with a CLR function, since you can get around the dynamic SQL, but even then, you have to specify the column names and data types for them in the function definition, so at best you can vary HOW you populate columns.
Look at a stored procedure to do this, OR, have a function return the query string, and execute it using the syntax Jean -Francois brought up.
(Edited for spelling)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 20, 2009 at 2:45 pm
yes, I have. I think Functions disallow the use of stored procedures in it. I used dynamic because I want to evaluate the week number if it is odd or even and from there change the expression in the where condition.
if there is a way to do this without using dynamic sql please lead me out of it.
Thanks.
July 20, 2009 at 2:57 pm
Try this on as the guts for your proc:
SELECT @WK = DATEPART(WEEK,MIN(TD)) FROM EDS_TD GROUP BY strEmpID
SET @bIsEven = CASE WHEN (@WK % 2)=0 THEN 1 ELSE 0 END
SELECT TOP 100 PERCENT T.* FROM (
SELECT
strEmpID,
MIN(TD) as Times,
DepCode as [Shift]
FROM EDS_TD
WHERE
DATEPART(dd,TD) IN (
SELECT DATEPART(day,MIN(TD))
FROM EDS_TD
)
AND DATEPART(hh,TD) BETWEEN 19 AND 24
AND InOut ='IN'
GROUP BY
strEmpID,
DepCode
UNION ALL
SELECT
strEmpID,
MAX(TD) as Times,
DepCode as [Shift]
FROM EDS_TD
WHERE DATEPART(dd,TD) IN (
SELECT DATEPART(day,DateADD(day,1,MIN(TD)))
FROM EDS_TD
)
AND DATEPART(hh,TD) BETWEEN 0 AND 10
AND InOut =''OUT''
GROUP BY
strEmpID,
DepCode) AS T
WHERE T.strEmpID IN
(
SELECT DISTINCT strEmpID
FROM EDS_TD
WHERE
--CHANGES ARE HERE
CASE when (@bIsEven=1) and DepCode='1st Grp Rep' then 1
when (@bIsEven=0) and DepCode in('2nd Grp Rep',
'2nd Grp Split 2AM',
'2nd Grp Split 1AM',
'2nd Grp Split 3AM'
) then 1
ELSE 0
END = 1
--CHANGES END HERE
)
ORDER BY T.strEmpID ASC
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 20, 2009 at 2:58 pm
Try something like this to get out of the dynamic SQL
SELECT TOP 100 PERCENT T.*
FROM ( SELECT strEmpID, MIN(TD) as Times, DepCode as [Shift]
FROM EDS_TD WHERE DATEPART(dd,TD)
IN ( SELECT DATEPART(day,MIN(TD)) FROM EDS_TD)
AND DATEPART(hh,TD) BETWEEN 19 AND 24
AND InOut ='IN'
GROUP BY strEmpID, DepCode
UNION ALL
SELECT strEmpID, MAX(TD) as Times, DepCode as [Shift]
FROM EDS_TD
WHERE DATEPART(dd,TD)
IN ( SELECT DATEPART(day,DateADD(day,1,MIN(TD))) FROM EDS_TD)
AND DATEPART(hh,TD) BETWEEN 0 AND 10
AND InOut ='OUT' GROUP BY strEmpID, DepCode) AS T
WHERE T.strEmpID IN (SELECT DISTINCT strEmpID FROM EDS_TD)
and (CASE WHEN DepCode = '1st Grp Rep' THEN 1
ELSE 2
END) =
(CASE WHEN (DATEPART(week,GETDATE())%2)=0 THEN 1 ELSE 2 END)
I hope this helps. I tried to pull this together quickly so there might be some work still to do. I used getdate() instead of your criteria but you should be able to adapt it.
July 21, 2009 at 8:46 am
Thank you all. I can now see how I may be able to return the data I need to avoid dynamic SQL. And I also learned that I can't do some stuffs in Functions.
Thanks. 😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply