User Defined Functions and EXEC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

User Defined Functions and EXEC

Author

Topic

thedryden
Starting Member

23 Posts

Posted - 2008-03-05 : 20:30:11

I'm using MS SQL Server 2005.

I want to simulate a table, using a Multi-statement Table-Value User-Defined Function, but I need the function build the SQL statement from scratch each time so I can dynamically define values like the table it references. The only way I know how to run the query after it has been defined in this manner is to run an EXEC command. However I'm getting an error basically saying that the EXEC command is off limits in a User Defined Function.

The Exsact Error is:

>[Error] Script lines: 1-108 ------------------------
 Invalid use of side-effecting or time-dependent operator in 'EXECUTE STRING' within a function.

Is there some way to get around this limitation of User Defined Functions. Or perhaps a way to simulate the functionality of Multi-statement Table-Value User-Defined Functions in a Store Procedure, specifically the ability to run where statements, or transform the data on the fly without re-writing the Stored Procedure every time.

The code I’m trying to run is below.

(Note: The code works as a stored procedure, so I'm sure that the core of the statment is correct)


CREATE FUNCTION [dbo].[TrendLine] 
( 
    @Summary as smallint,
    @Start as datetime,
    @End as datetime,
    @Table as varchar(100),
    @X as varchar(100),
    @Count as varchar(100),
    @Duration as varchar(100)
)
RETURNS 
	@TrendLineTable table (
		DATE_DAY datetime
        , EQ_REGION varchar(25)
        , EQ_MARKET_CLUSTER varchar(30)
        , Y float
        , X int
        , DURATION float
        , FORMULA varchar(100)
        , a float
        , b float
        , EX int
        , EY float
        , EX2 int
        , EXY float
        , N int
	)
AS
BEGIN

DECLARE @SQL as varchar(3000)
    , @CountText as varchar(150)
    , @StartText as varchar(50)
    , @EndText as varchar(50)

SET @StartText = 'cast( ' + char(39) + cast( @Start as varchar(20) ) + char(39) + ' as datetime ) '
SET @EndText ='cast( ' + char(39) +  cast( @End as varchar(20) ) + char(39) + ' as datetime ) '
IF @Summary = 1
BEGIN
    SET @CountText = 'sum'
END
ELSE
BEGIN
    SET @CountText = 'count'
END

SET @SQL = 'INSERT INTO @TrendLineTable
DECLARE TrendlineC cursor for
SELECT a.DATE_DAY
    , s2.EQ_REGION
    , s2.EQ_MARKET_CLUSTER
    , ( ( EY - ( b * EX ) ) / N ) + ( b * X ) AS Y
    , X
    , Y AS DURATION
    , cast( b as varchar(100) ) + ' + char(39) + 'x + ' + char(39) + ' + cast(  ( EY - ( b * EX ) ) / N  as varchar(100) ) AS FORMULA
    , ( EY - ( b * EX ) ) / N AS a
    , b
    , EX
    , EY
    , EX2
    , EXY
    , N    
FROM (
    SELECT EQ_REGION
        , EQ_MARKET_CLUSTER
        , sum( X ) AS EX
        , sum( Y ) AS EY
        , sum( X2 ) AS EX2
        , sum( XY ) AS EXY
        , count( X ) AS N
        , ( ( count( X ) * sum( XY ) ) - ( sum( X ) * sum( Y ) ) ) / ( ( count( X ) * sum( X2 ) ) - POWER( sum( X ), 2 ) ) AS b
    FROM (
        SELECT ' + @X + ' AS DATE_DAY
            , EQ_REGION
            , EQ_MARKET_CLUSTER
            , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
            , cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
            , POWER( row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + '  ), 2) X2
            , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) * cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + ' ( ' + @Count + ' ) AS XY
        FROM ' + @Table + '
        WHERE ' + @X + ' >= ' + @StartText + '
            AND ' + @X + ' < ' + @EndText + '
        GROUP BY ' + @X + '
            , EQ_REGION
            , EQ_MARKET_CLUSTER
    ) s1
    GROUP BY EQ_REGION
        , EQ_MARKET_CLUSTER
) s2
INNER JOIN (
    SELECT ' + @X + ' AS DATE_DAY
        , EQ_MARKET_CLUSTER
        , row_number() over (partition by EQ_MARKET_CLUSTER order by ' + @X + ' ) AS X
        , cast( sum( ' + @Duration + ' ) as float ) / ' + @CountText + '( ' + @Count + ' ) AS Y
    FROM ' + @Table + '
    WHERE ' + @X + ' >= ' + @StartText + '
        AND ' + @X + ' < ' + @EndText + '
    GROUP BY ' + @X + '
        , EQ_REGION
        , EQ_MARKET_CLUSTER
    ) a ON s2.EQ_MARKET_CLUSTER=a.EQ_MARKET_CLUSTER'

EXEC ( @SQL )
	
	RETURN
END

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 20:39:05

Try making that a PROCEDURE instead of a function.

There are restrictions to what can be done in a function..dynamic SQL AND cursors in a function is pretty inefficient..

maybe if you explained what this is supposed to do...there is a function based alternative/option

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-06 : 08:02:17

too much dynamic sql???

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources