question

ronbarlow-5250 avatar image
0 Votes"
ronbarlow-5250 asked ronbarlow-5250 commented

Dynamic SQL in UDF - Work around

Hello everyone

I have a 16 period fields in a schema, for example PreviousMonth where each date in the previous month has a value of 1
I trying to write a function that returns the Max date key dependant on the Field I pass in PreviousMonth, NextMothh etc

I have the code below, where the SQL string is dynamic. I have read that you cannot have dynamic SQL in a UDF,
What’s the best way to return the Max date of each period, as I’m trying to write a query to determine if the Max date is between two other date fields.

I also tried a stored procedure, but again I read you cannot include a stored procedure in a query…

Alter FUNCTION dbo.ufnGetMaxPeriodDate(@Period Varchar(50))
RETURNS int
AS

BEGIN
DECLARE @ret int;
DECLARE @SQL nvarchar(Max)

 set @SQL='    SELECT    MAX(DateKey) AS MaxDate            
     FROM dbo.DimDate d
  WHERE d.'+@Period+'=1 '

EXECUTE SP_EXECUTESQL @SQL;

Set @ret=@SQL

  IF (@ret IS NULL)   
     SET @ret = 0;  
 RETURN @ret;  

END;


DECLARE @returnvalue int;
EXEC @returnvalue = dbo.ufnGetMaxPeriodDate('PreviousMonth')
PRINT @returnvalue
Thanks in Advance
Ron

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered ronbarlow-5250 commented

You cannot execute the dynamic sql script inside the function. Try this:

 CREATE OR ALTER FUNCTION dbo.ufnGetMaxPeriodDate(@Column Varchar(50))
 RETURNS int
 AS
 BEGIN
     DECLARE @MaxDate int;
    
     SELECT @MaxDate = MAX(DateKey)
       FROM dbo.DimDate
      WHERE (Col01 = 1 AND @Period = 'Col01') 
         OR (Col02 = 1 AND @Period = 'Col02')
         OR (Col03 = 1 AND @Period = 'Col03')
         -- Add the columns to be checked
    
     RETURN @MaxDate;
 END
 GO
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

There is no way to do that in a function.

What you will need to do is something like:

WHERE (@period = 'XXX' AND d.XXX = 1)
OR (@period = 'YYY' AND d.YYY = 1)
OR @period = 'ZZZ' AND d.ZZZ = 1)
...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JoeCelko-6699 avatar image
0 Votes"
JoeCelko-6699 Suspended answered

I have a 16 period fields [sic] in a schema, for example PreviousMonth where each date in the previous month has a value of 1. I am trying to write a function that returns the Max date key dependant on the field [sic] I pass in PreviousMonth, NextMothh etc <<

The first and the simplest that you don't understand that we hate dynamic SQL and that it's not designed to do dynamic SQL inside a function or procedure. You are misusing a good tool. Next, your mindset is still stuck in coding in a procedural language! SQL is a declarative language. We hate to loops, if – then – else selection constructs, local variables, and all the other things you had back in procedural languages. I would guess that what you need is a simple lookup table in the form of calendar.

I also see that you don't understand that a column and a field are totally different things; a field is a subset of a column that still has meaning. For example the year, month and day are what the ANSI/ISO standards call fields, and they belong to a given date column.

I have the code below, where the SQL string is dynamic. I have read that you cannot have dynamic SQL in a UDF<<

It's not just that you can have dynamic SQL, the problem is that you shouldn't be writing a UDF at all. Since the rule about posting DDL doesn't seem to apply to you, I am going to make some guesses.

CREATE TABLE Calendar
(cal_date DATE NOT NULL PRIMARY KEY,
foobar_period_nbr INTEGER NOT NULL,
ordinal_day INTEGER NOT NULL,
ordinal_business_day INTEGER NOT NULL,
..);

A calendar that's good for 100 years will only have 36,525 rows in it and by today's standards will be quite small. Things like previous month, next month and other relative temporal shifts are really bad programming. It's like storing age instead of a birth date. There are a lot of other tricks you can do with a calendar table, but let's not get into them yet.

You might want to get a copy of temporal queries in SQL is a free download of a UDF from the University of Arizona. It's a good classic book written by Rick Snodgrass.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.