August 21, 2009 at 7:15 am
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.
August 26, 2009 at 10:18 am
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
August 26, 2009 at 11:43 am
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
August 27, 2009 at 12:13 am
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"
August 27, 2009 at 1:09 am
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?
August 28, 2009 at 2:07 am
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"
August 28, 2009 at 3:33 am
Shall we use DML (INSERT, UPDATE, DELETE) inside a function?
Shall we use DDL inside a function?
August 31, 2009 at 5:59 am
You can do DML and DDL operations only on the table variables defined in the function.
"Keep Trying"
August 31, 2009 at 6:16 am
Thnaks Chirag.
Grt Answer
August 31, 2009 at 7:08 am
[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
August 31, 2009 at 11:27 pm
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?
September 1, 2009 at 12:03 am
[font="Verdana"]To do this you need to use Dynamic SQL inside the SProc.
Mahesh[/font]
MH-09-AM-8694
September 1, 2009 at 12:08 am
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