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?

Solution 1

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?

Solution 2

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.

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

Solution 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.

Solution 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.

View more solutions

Comments

[KM.]

for an actual way to do this, see: stackoverflow.com/a/10721985/65223

[Alexey Shevelyov]

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

[Mister_Tom]

I like this method, very creative. Just return the debug output as another column in return table :-).

[Jan]

Don't forget to run beforehand: EXEC sp_configure'xp_cmdshell', 1 GO RECONFIGURE GO

[Sirke]

Don't forget to run beforehand: EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO

[access_granted]

or make it a stored procedure

[Roel]

what if the value you want to trace is an int?

[Jana Sattainathan]

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

[dhiman]

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)

[tsilb]

Sensible chuckle. When you don't give us a convenient "right" way, we'll invent a convenient "wrong" way.