Dynamic Function Issue

Question

Hi All,

I am trying to create some dynamic DDL to build a function and when I run it, it keeps giving me an error. I am not sure what I am doing wrong with the format....I have tried a lot of different things and now it is just out of curiousity that I want to understand how to get it to work. Any input is greatly appreciated.

CODE:

DECLARE @SQL nvarchar(max) = 
        'ALTER FUNCTION dbo.GetFiscalDate() 
            RETURNS DATETIME 
         AS 
         BEGIN 
            DECLARE @RESULT DATETIME
            SELECT @RESULT = @FY
            RETURN @RESULT;
         END'
   ,@FY datetime = '01/01/2016'
   ,@ParamDef nvarchar(50) = N'@FY datetime'
exec sp_executesql @SQL,@ParamDef,@FY

Gives me this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FUNCTION'.
Msg 178, Level 15, State 1, Line 7
A RETURN statement with a return value cannot be used in this context.

This Code however...works:

DECLARE
 @FY nvarchar(10) = '01/01/2015'
,@SQL nvarchar(max) 

Select @SQL = 
        'ALTER FUNCTION dbo.GetFiscalDate() 
            RETURNS DATETIME 
         AS 
         BEGIN 
            DECLARE @RESULT DATETIME
            SELECT @RESULT = ' + @FY + '
            RETURN @RESULT;
         END'
exec sp_executesql @SQL

What am I missing with this when I want to pass in params instead of concatenating them with the statement?

As usual I greatly appreciate all input.

Thanks,

S

Answer 1

parameters are used in execution plans so that you can reuse the execution plan, not in DDL statements, use your 2nd approach

Answer 2

I don't see where in your string you're including a call to your function. Whatever sql you put in your string should exec directly in a query window, and altering a function and then listing the params wouldn't do it. You have to exec the function with the params listed in their normal syntax.

Why not pass the @FY variable as a parameter into the function?

I'm not sure what you're trying to do, but I wouldn't expect the approach you've described above to work.