SQL Functions - Logging [duplicate]

问题:

This question already has answers here:

Closed 7 years ago.

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, execute a print statement, or even raise an error from it.

SQL returns the message

Invalid use of a side-effecting operator 'PRINT' within a function.

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

回答1:

try:

DECLARE @ExecuteString   varchar(500)
       ,@Yourtext        varchar(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

回答2:

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

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