Please help on How to return Dynamic SQL results using table-valued function

  • 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

  • Hi,

    Have you tried this?

    exec sp_ExecuteSQL @vsql

    Cheers,

    J-F

  • 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

  • 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?

  • 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.

  • 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?

  • 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.

  • 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