TSQL How do you output PRINT in a user defined function?

c00ke_31766 0
Viewed 140953

Basically I want to use PRINT statement inside a user defined function to aide my debugging.

However I'm getting the following error;

Invalid use of side-effecting or time-dependent operator in 'PRINT' within a function.

Can this not be done?

Anyway to aid my user defined function debugging?

user-defined-functions tsql
8 Answers

No, sorry. User-defined functions in SQL Server are really limited, because of a requirement that they be deterministic. No way round it, as far as I know.

Have you tried debugging the SQL code with Visual Studio?

user_320500

I have tended in the past to work on my functions in two stages. The first stage would be to treat them as fairly normal SQL queries and make sure that I am getting the right results out of it. After I am confident that it is performing as desired, then I would convert it into a UDF.

TheTXI_221640

No, you can not.

You can call a function from a stored procedure and debug a stored procedure (this will step into the function)

user_551590

On my opinion, whenever I want to print or debug a function. I will copy the content of it to run as a normal SQL script. For example

My function:

create or alter function func_do_something_with_string(@input nvarchar(max)) returns nvarchar(max)
as begin
   -- some function logic content
   declare @result nvarchar(max)
   set @result = substring(@input , 1, 10)
   -- or do something else

   return @result
end

Then I just copy and run this out of the function to debug

    declare @input nvarchar(max) = 'Some string'      

    -- some function logic content
    declare @result nvarchar(max)
    set @result = substring(@input , 1, 10)
    -- this line is added to check while debugging
    print @result 
    
    -- or do something else
    -- print the final result
    print @result
user_25205090
Related