SQL Functions - Logging

Question

Possible duplicate:
TSQL How do you print the PRINT in a user-defined function?

It drives me crazy.

I have a function that I am trying to debug, but I cannot insert it into a table, execute a print instruction, or even output an error from it.

SQL returns a message

Incorrect use of the "PRINT" operator that performs side effects within a function.

How can I debug this, I just want to know the value of a variable while it is running?

Answer 1

to try:

DECLARE @ExecuteString varchar(500) ,@Yourtext varchar(500) @ExecuteString = 'echo '+@Yourtext+' > yourfile.txt) exec master..xp_cmdshell @ExecuteString, no_output 

use: ">" to create / overwrite the file and "→" to create / add to the file. you may need to use REPLACE(@Yourtext,'&','^&') to exit & char

Answer 2

If your function returns a string, you can always insert a return, for example.

 ALTER FUNCTION dbo.whatever() RETURNS NVARCHAR(128) AS BEGIN DECLARE @foo VARCHAR(128); SELECT TOP (1) @foo = name FROM sys.objects ORDER BY NEWID(); -- temporary debug: RETURN (@foo); ... continue with function END GO 

If the variable is numeric or date, you can use RTRIM () or CONVERT () to safely convert it to a string first.

You can do something similar with a table function, just add a column in which you can place any debug message or variable that you want to output.