Function debugging

  • Hi,

    How to debug a function in SQL server? The traditional 'print' or 'SELECT' is not working inside functions. Even its not allowing me to INSERT values in a temp table (?).

    Thanks in advance.

  • I usually try to make the query work outside the function first.

    For example let's say i want to write a function which gives me monthNumber of the date passed to it.

    DECLARE @date smalldatetime

    SET @date = GETDATE()

    SELECT DATEPART(mm,@date)

    Once I have it working I changed it to a function

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -------------------------

    -- Author: Amit Gaur

    -- Date: Aug 26 09

    -------------------------

    CREATE FUNCTION [dbo].extractMonthNumberFromDate

    (

    @date smalldatetime

    )

    RETURNS [int]

    AS

    BEGIN

    DECLARE @monthNumber int

    SELECT @monthNumber = DATEPART(mm,@date)

    RETURN @monthNumber;

    END

    GO

    for testing

    DECLARE @date smalldatetime

    SET @date = GETDATE()

    SELECT dbo.extractMonthNumberFromDate(@date)

    While debugging i usually dumb it down and move up.

    If you give more information, i can help you debug it

  • Yep. Pull it outside the function. Define the parameters are variables. Debug your usual way.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Here's a different approach that i try for complex functions. I create a stored procedure with same code as that of the function. Most of the time this is easily done by making a few small changes. Then you can debug it just like a procedure.

    "Keep Trying"

  • But Chirag,

    You cant call a function from a SELECT statement and send a column name as a parameter.

    Shall we sens a column name as a parameter in SP?

  • You cant call it in a select statement not can you pass column names . You will have to define column name as a params or hard code the column name in the code to do the debugging. As i said do it only if your function is complex other wise just pull the code out as others mentioned before.

    "Keep Trying"

  • Shall we use DML (INSERT, UPDATE, DELETE) inside a function?

    Shall we use DDL inside a function?

  • You can do DML and DDL operations only on the table variables defined in the function.

    "Keep Trying"

  • Thnaks Chirag.

    Grt Answer

  • [font="Verdana"]

    arup_kc (8/27/2009)


    But Chirag,

    You cant call a function from a SELECT statement and send a column name as a parameter.

    Shall we sens a column name as a parameter in SP?

    I didn't get you arup. why cant you call a function from/in a select statement? function can be called from/in select statement.

    Like: Select dbo.fn_FunctionName(ColumnName) As AliasName From TableName

    Mahesh[/font]

    MH-09-AM-8694

  • Mahesh Bote (8/31/2009)


    [font="Verdana"]

    arup_kc (8/27/2009)


    But Chirag,

    You cant call a function from a SELECT statement and send a column name as a parameter.

    Shall we sens a column name as a parameter in SP?

    I didn't get you arup. why cant you call a function from/in a select statement? function can be called from/in select statement.

    Like: Select dbo.fn_FunctionName(ColumnName) As AliasName From TableName

    Mahesh[/font]

    Sorry Mahesh, its a typo error. I was going to tell that we cant call SP from SELECT statement. Can we send a column name as an input parameter in a SP?

  • [font="Verdana"]To do this you need to use Dynamic SQL inside the SProc.

    Mahesh[/font]

    MH-09-AM-8694

  • Mahesh Bote (9/1/2009)


    [font="Verdana"]To do this you need to use Dynamic SQL inside the SProc.

    Mahesh[/font]

    Thnaks mahesh for ur reply...I wll try the method.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply