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

User · Answer

Use extended procedure xp cmdshell to run a shell command  I used it to print output to a file   exec xp cmdshell  echo  mytextoutput   gt  gt  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

User · Answer

No  you can not   You can call a function from a stored procedure and debug a stored procedure  this will step into the function

User · Answer

You can try returning the variable you wish to inspect  E g  I have this function   --Contencates seperate date and time strings and converts to a datetime  Date should be in format 25 03 2012  Time as 9 18 25  ALTER FUNCTION  dbo   ufn GetDateTime    date nvarchar 11    time nvarchar 11   RETURNS datetime AS BEGIN          --select dbo ufn GetDateTime  25 03 2012     9 18 25        declare  datetime datetime      declare  day part nvarchar 3      declare  month part nvarchar 3      declare  year part nvarchar 5       declare  point ix int      set  point ix   charindex       date      set  day part   substring  date  0   point ix       set  date   substring  date   point ix  len  date  -  point ix      set  point ix   charindex       date       set  month part   substring  date  0   point ix       set  date   substring  date   point ix  len  date  -  point ix      set  point ix   charindex       date       set  year part   substring  date  0   point ix       set  datetime    month part    day part     year part          time      return  datetime END   When I run it   I get  Msg 241  Level 16  State 1  Line 1 Conversion failed when converting date and or time from character string   Arghh    So  what do I do   ALTER FUNCTION  dbo   ufn GetDateTime    date nvarchar 11    time nvarchar 11   RETURNS nvarchar 22  AS BEGIN          --select dbo ufn GetDateTime  25 03 2012     9 18 25        declare  day part nvarchar 3      declare  point ix int      set  point ix   charindex       date      set  day part   substring  date  0   point ix       return  day part END   And I get  25   So  I am off by one and so I change to    set  day part   substring  date  0   point ix   1    Voila  Now it works

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[tsql] TSQL How do you output PRINT in a user defined function?

Examples related to tsql

Examples related to user-defined-functions