How to use sql UserDefined Funtion in Asp.Net
-
Hi , m Create A UserDefined funtion in sqlserver 2000 , And Want to use this in Asp.net ; I use Stored Procedure 4 this by this code but...... CREATE PROCEDURE [dbo].[spShow_Total_Holiday_In_Current_Month] @day varchar(100) AS select **dbo.fnGetDatesforAday('10/01/2008','10/31/2008' , @day)**GO But this shows error "Invalid object name 'Attandance.dbo.fnGetDatesforAday' " tell me how can i solve this . Thanx
-
Hi , m Create A UserDefined funtion in sqlserver 2000 , And Want to use this in Asp.net ; I use Stored Procedure 4 this by this code but...... CREATE PROCEDURE [dbo].[spShow_Total_Holiday_In_Current_Month] @day varchar(100) AS select **dbo.fnGetDatesforAday('10/01/2008','10/31/2008' , @day)**GO But this shows error "Invalid object name 'Attandance.dbo.fnGetDatesforAday' " tell me how can i solve this . Thanx
Hi i hope you don't have the function dbo.fnGetDatesforAday. so that u got the error. If u run the query manually in your DB server you will get the same error. "select dbo.fnGetDatesforAday('10/01/2008','10/31/2008' , @day) Go".
-
Hi i hope you don't have the function dbo.fnGetDatesforAday. so that u got the error. If u run the query manually in your DB server you will get the same error. "select dbo.fnGetDatesforAday('10/01/2008','10/31/2008' , @day) Go".
You will get the result if you create the below fuction in your DB server. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: To Get any weekday dates between two dates -- ============================================= CREATE FUNCTION fnGetDatesforAday ( -- Add the parameters for the function here @DtFrom DATETIME, @DtTo DATETIME, @DayName VARCHAR(12) ) RETURNS @DateList TABLE ([Day] varchar(20),Dt datetime) AS BEGIN IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday' OR @DayName = 'Friday' OR @DayName = 'Saturday') BEGIN --Error Insert the error message and return INSERT INTO @DateList SELECT 'Invalid Day',NULL AS DAT RETURN END DECLARE @TotDays INT DECLARE @CNT INT SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates] SET @CNT = 0 WHILE @TotDays >= @CNT -- repeat for all days BEGIN -- Pick each single day and check for the day needed IF DATENAME(DW, (@DTTO - @CNT)) = @DAYNAME BEGIN INSERT INTO @DateList SELECT @DAYNAME,(@DTTO - @CNT) AS DAT END SET @CNT = @CNT + 1 END RETURN END GO
-
You will get the result if you create the below fuction in your DB server. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Description: To Get any weekday dates between two dates -- ============================================= CREATE FUNCTION fnGetDatesforAday ( -- Add the parameters for the function here @DtFrom DATETIME, @DtTo DATETIME, @DayName VARCHAR(12) ) RETURNS @DateList TABLE ([Day] varchar(20),Dt datetime) AS BEGIN IF NOT (@DayName = 'Monday' OR @DayName = 'Sunday' OR @DayName = 'Tuesday' OR @DayName = 'Wednesday' OR @DayName = 'Thursday' OR @DayName = 'Friday' OR @DayName = 'Saturday') BEGIN --Error Insert the error message and return INSERT INTO @DateList SELECT 'Invalid Day',NULL AS DAT RETURN END DECLARE @TotDays INT DECLARE @CNT INT SET @TotDays = DATEDIFF(DD,@DTFROM,@DTTO)-- [NO OF DAYS between two dates] SET @CNT = 0 WHILE @TotDays >= @CNT -- repeat for all days BEGIN -- Pick each single day and check for the day needed IF DATENAME(DW, (@DTTO - @CNT)) = @DAYNAME BEGIN INSERT INTO @DateList SELECT @DAYNAME,(@DTTO - @CNT) AS DAT END SET @CNT = @CNT + 1 END RETURN END GO
Thanx . but i create the same Function it works fine , but when i call this function under stored Procedure then it show error .