Sql Functions - Logging

Possible Duplicate: TSQL How do you output PRINT in a user defined function? This is driving me crazy. I have a function I am trying to debug, but I can't insert into a table, e

Solution 1:

try:

DECLARE@ExecuteStringvarchar(500)
       ,@Yourtextvarchar(500)

@ExecuteString='echo  '+@Yourtext+' > yourfile.txt)

exec master..xp_cmdshell @ExecuteString, no_output

use: ">" to create/overwrite file, and ">>" to create/append onto file. you might need to use REPLACE(@Yourtext,'&','^&') to escape the & char

Solution 2:

If your function returns a string, you could always interject a return e.g.

ALTERFUNCTION dbo.whatever()
RETURNS NVARCHAR(128)
ASBEGINDECLARE@fooVARCHAR(128);

  SELECT TOP (1) @foo= name FROM sys.objects ORDERBY NEWID();

  -- temporary debug:RETURN (@foo);

  ... continue withfunctionEND
GO

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

You could do something similar with a table-valued function, just add a column where you can place whatever debug message or variable you want to output.