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

Question

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?

Answer 1

Tip: generate error.

declare @Day int, @Config_Node varchar(50)

    set @Config_Node = 'value to trace'

    set @Day = @Config_Node

You will get this message:

Conversion failed when converting the varchar value 'value to trace' to data type int.

Answer 2

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?

Answer 3

I got around this by temporarily rewriting my function to something like this:

IF OBJECT_ID ('[dbo].[fx_dosomething]', 'TF') IS NOT NULL
  drop function [dbo].[fx_dosomething];
GO

create FUNCTION dbo.fx_dosomething ( @x numeric )
returns @t table (debug varchar(100), x2 numeric)
as
begin
 declare @debug varchar(100)
 set @debug = 'printme';

 declare @x2 numeric
 set @x2 = 0.123456;

 insert into @t values (@debug, @x2)
 return 
end
go

select * from fx_dosomething(0.1)

Answer 4

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.

Answer 5

Use extended procedure xp_cmdshell to run a shell command. I used it to print output to a file:

exec xp_cmdshell 'echo "mytextoutput" >> c:\debuginfo.txt'

This creates the file debuginfo.txt if it does not exist. Then it adds the text "mytextoutput" (without quotation marks) to the file. Any call to the function will write an additional line.

You may need to enable this db-server property first (default = disabled), which I realize may not be to the liking of dba's for production environments though.

Answer 6

No, you can not.

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

Answer 7

You can try returning the variable you wish to inspect. E.g. I have this function:

--Contencates seperate date and time strings and converts to a datetime. Date should be in format 25.03.2012. Time as 9:18:25.
ALTER FUNCTION [dbo].[ufn_GetDateTime] (@date nvarchar(11), @time nvarchar(11))
RETURNS datetime
AS
BEGIN

        --select dbo.ufn_GetDateTime('25.03.2012.', '9:18:25')

    declare @datetime datetime

    declare @day_part nvarchar(3)
    declare @month_part nvarchar(3)
    declare @year_part nvarchar(5)

    declare @point_ix int

    set @point_ix = charindex('.', @date)
    set @day_part = substring(@date, 0, @point_ix)

    set @date = substring(@date, @point_ix, len(@date) - @point_ix)
    set @point_ix = charindex('.', @date)

    set @month_part = substring(@date, 0, @point_ix)

    set @date = substring(@date, @point_ix, len(@date) - @point_ix)
    set @point_ix = charindex('.', @date)

    set @year_part = substring(@date, 0, @point_ix)

    set @datetime = @month_part + @day_part  + @year_part + ' ' + @time

    return @datetime
END

When I run it.. I get: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

Arghh!!

So, what do I do?

ALTER FUNCTION [dbo].[ufn_GetDateTime] (@date nvarchar(11), @time nvarchar(11))
RETURNS nvarchar(22)
AS
BEGIN

        --select dbo.ufn_GetDateTime('25.03.2012.', '9:18:25')

    declare @day_part nvarchar(3)
    declare @point_ix int

    set @point_ix = charindex('.', @date)
    set @day_part = substring(@date, 0, @point_ix)

    return @day_part
END

And I get '25'. So, I am off by one and so I change to..

set @day_part = substring(@date, 0, @point_ix + 1)

Voila! Now it works :)

Answer 8

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