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;

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

Can this not be done?

Anyway to aide my user defined function debugging?

Cheers

Answer 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?

Answer 2

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 3

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 4

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)