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?

for an actual way to do this, see: http://stackoverflow.com/a/10721985/65223 — KM., May 23 '12 at 14:36
Whatever value you are trying to test - just declare a variable that you will remove later and return it as a part of the dataset. Just another option — Alexey Shevelyov, Jun 05 '18 at 21:50

score 59 · Answer 1 · answered Feb 07 '13 at 14:06

59

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.

answered Feb 07 '13 at 14:06

Estevez

1,014
8
13

1

what if the value you want to trace is an int? – Roel Jul 23 '18 at 14:56
1

really nice trick.. I had this problem forever!! Roel to trace an int value, you can try select @day = 'int value# ' + convert(varchar(10), @int_value_to_trace) – dhiman Aug 16 '19 at 17:12
1

Sensible chuckle. When you don't give us a convenient "right" way, we'll invent a convenient "wrong" way. – tsilb Apr 16 '21 at 21:33

score 40 · Accepted Answer · answered Feb 25 '09 at 16:07

40

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?

answered Feb 25 '09 at 16:07

Tor Haugen

19,509
9
45
63

score 33 · Answer 3 · edited Oct 03 '09 at 22:31

33

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)

edited Oct 03 '09 at 22:31

sth

222,467
53
283
367

answered Oct 03 '09 at 19:42

1

I like this method, very creative. Just return the debug output as another column in return table :-). – Mister_Tom Nov 07 '11 at 21:25

score 28 · Answer 4 · answered Feb 25 '09 at 16:09

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.

score 15 · Answer 5 · answered Oct 07 '14 at 14:38

15

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.

answered Oct 07 '14 at 14:38

LDerckx

163
1
8

2

Don't forget to run beforehand: EXEC sp_configure'xp_cmdshell', 1 GO RECONFIGURE GO – Jan Apr 19 '17 at 07:33
Don't forget to run beforehand: EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO – Irawan Soetomo Apr 25 '17 at 03:46
To print the datetime and a comment to the file you could do something like this: EXEC xp_cmdshell 'echo %DATE%_%TIME% Processing something >> d:\Log\debuginfo.log' This will create an output in the file that looks like: Thu 02/21/2019_18:53:10.18 Processing something – Jana Sattainathan Feb 21 '19 at 23:54

score 4 · Answer 6 · answered Feb 25 '09 at 16:08

4

No, you can not.

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

answered Feb 25 '09 at 16:08

Quassnoi

413,100
91
616
614

or make it a stored procedure – access_granted Mar 02 '18 at 03:14

score 1 · Answer 7 · answered May 12 '21 at 04:33

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

This does not work when you are using recursion. – Pawel Wujczyk Mar 08 '23 at 19:52 — Pawel Wujczyk, Mar 08 '23 at 19:52

score 0 · Answer 8 · answered Nov 09 '12 at 11:23

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 :)

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

8 Answers8

Linked

Related