Writing a dynamic SQL function

Question

I am having a few problems trying to return a table from a SQL function, where the SQL to create the table is written dynamically.

So far I have:

CREATE FUNCTION dbo.SEL_PCD
(
    @COBDate AS DATETIME,
    @FileName AS VARCHAR(50),
    @PC AS VARCHAR(50),
    @MyList AS VARCHAR(max),

    DECLARE @SQL VARCHAR(max)

    SET @SQL = 'SELECT * FROM 
                    (SELECT tab1.TID FROM
                        (SELECT TID FROM dbo.SEL_RT('+@COBDate+','+@FileName+') WHERE BID IN ('+ @MyList +')) tab1
                    JOIN
                        (SELECT TID FROM CT WHERE (Col_Name LIKE %' + @PC + '% OR Bk LIKE %' + @PC + '%) AND FileName = ' + @FileName + ' AND COBDate = @COBDate) tab2
                    ON tab1.TID = tab2.TID) tab3
                JOIN
                    (SELECT TID, Value FROM CR WHERE BID IN (' + @MyList + ') AND COBDate = ' + @COBDate + ' AND FileName = ' + @FileName + 'AND ScenID = 266) tab7
                ON tab3.TID = tab7.TID'

)
RETURNS TABLE AS
RETURN
(
    EXEC sp_executesql @SQL
)
GO

I am getting errors declaring the SQL variable. Am I ok to return the table via the execute command?

fancyPants · Accepted Answer · 2012-06-28 11:30:24Z

Have a try with this one:

CREATE PROCEDURE dbo.SEL_PCD
(
    @COBDate DATETIME,
    @FileName VARCHAR(50),
    @PC VARCHAR(50),
    @MyList VARCHAR(max)
) AS

    DECLARE @SQL VARCHAR(max)

    SELECT @SQL = 'SELECT * FROM 
                    (SELECT tab1.TID FROM
                        (SELECT TID FROM dbo.SEL_RT('+@COBDate+','+@FileName+') WHERE BID IN ('+ @MyList +')) tab1
                    JOIN
                        (SELECT TID FROM CT WHERE (Col_Name LIKE %' + @PC + '% OR Bk LIKE %' + @PC + '%) AND FileName = ' + @FileName + ' AND COBDate = @COBDate) tab2
                    ON tab1.TID = tab2.TID) tab3
                JOIN
                    (SELECT TID, Value FROM CR WHERE BID IN (' + @MyList + ') AND COBDate = ' + @COBDate + ' AND FileName = ' + @FileName + 'AND ScenID = 266) tab7
                ON tab3.TID = tab7.TID'

EXEC(@SQL)

Functions

can be used with Select statement
Not returning output parameter but returns Table variables
You can join UDF
Cannot be used to change server configuration
Cannot be used with XML FOR clause
Cannot have transaction within function

Stored Procedure

have to use EXEC or EXECUTE
return output parameter
can create table but won’t return Table Variables
you can not join SP
can be used to change server configuration
can be used with XML FOR Clause
can have transaction within SP

Difference between Functions and Stored Procedures source: sqlservercentral.com/Forums/Topic416974-8-1.aspx I admit, I was lazy

MatBailie · Accepted Answer · 2012-06-28 11:48:24Z

You can't call stored procedures from within a function, including the stored procedures EXECUTE or SP_EXECUTESQL. This means that you can't have dynamic sql embedded within a function.

The reason you can't call stored procedures is because functions are not allowed to have side-effects (calling them can't in itself change any data - they can't insert, update or delete). But stored procedures can. This means that a function that calls a stored procedure would suddenly become able to have side-effects.

SP's can call Functions, not the other way around.

Also, SQL is compiled to an execution plan. At that time the tables and indexes that are being used all become fixed. If a function includes dynamic sql that would be possible; the tables, etc, that are to be used are not known at compile time, and SQL does not have that capability.

In your case the only part of your query that seems to need Dynamic SQL is that you are passing a comma delimited list as the @myList parameter. There is, however, an alternative approach.

Look for one of the many dbo.fn_split() functions that are available on line (and many on SO). Then use that function to join on the data...

CREATE FUNCTION dbo.SEL_PCD( @COBDate     AS DATETIME,
                             @FileName    AS VARCHAR(50),
                             @PC          AS VARCHAR(50),
                             @MyList      AS VARCHAR(max)
                            )
RETURNS TABLE
AS
RETURN
  SELECT
    CR.TID,
    CR.Value
  FROM
    dbo.SEL_RT(@COBDate, @FileName) AS RT
  INNER JOIN
    CT
     ON CT.TID = RT.TID
  INNER JOIN
    CR
     ON CR.TID = RT.TID
  WHERE
    (CT.Col_Name LIKE '%'+@PC+'%' OR CT.Bk LIKE '%'+@PC+'%')
    AND CT.FileName = @FileName
    AND CT.COBDate  = @COBDate
    AND CR.FileName = @FileName
    AND CR.COBDate  = @COBDate
    AND CR.ScenID   = 266
    AND RT.BID IN (SELECT id FROM dbo.fn_split(@myList, ',') AS my_list)
    AND CR.BID IN (SELECT id FROM dbo.fn_split(@myList, ',') AS my_list)

Asif · Accepted Answer · 2012-06-28 11:38:05Z

you cannot use execute command in function

CREATE PROCEDURE dbo.SEL_PCD4
(
    @COBDate AS DATETIME,
    @FileName AS VARCHAR(50),
    @PC AS VARCHAR(50),
    @MyList AS VARCHAR(max)
) AS
    DECLARE @SQL VARCHAR(max)

    SET @SQL = 'SELECT * FROM 
                    (SELECT tab1.TID FROM
                        (SELECT TID FROM dbo.SEL_RT('+@COBDate+','+@FileName+') WHERE BID IN ('+ @MyList +')) tab1
                    JOIN
                        (SELECT TID FROM CT WHERE (Col_Name LIKE %' + @PC + '% OR Bk LIKE %' + @PC + '%) AND FileName = ' + @FileName + ' AND COBDate = @COBDate) tab2
                    ON tab1.TID = tab2.TID) tab3
                JOIN
                    (SELECT TID, Value FROM CR WHERE BID IN (' + @MyList + ') AND COBDate = ' + @COBDate + ' AND FileName = ' + @FileName + 'AND ScenID = 266) tab7
                ON tab3.TID = tab7.TID'

    EXEC sp_executesql @SQL

GO

Collectives™ on Stack Overflow

Writing a dynamic SQL function

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related