CODEWITHSUNDEEP
sql-serverfunctionstored-procedures
A.Goutam
A.Goutam

Reputation: 3494

Return value from function in SQL Server

I want to get the last Saturday from today + 21 days.

In order to achieve this, I have written this script shown below. But the problem is that I can't get success to return the value from the result.

I want to create this function in SQL Server and will get this value in a stored procedure where I want. 

DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME 
DECLARE @NumOfDays INT
DECLARE @resultDate smalldatetime

SET @StartDate = GETDATE()
SET @EndDate = GETDATE()+21
SET @NumOfDays = DATEDIFF(DD,@StartDate , @EndDate) + 1 ;

WITH Tens AS
(
    SELECT 1 N UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 UNION ALL 
    SELECT 1 
),
HUNDREDS AS
(
    SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
),
THOUSANDS AS
(
    SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
),
Numbers AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS
)
SELECT TOP 1  
    DATEADD(DD, (RN - 1), @StartDate) SaturdayDates
FROM 
    Numbers
WHERE 
    RN <= @NumOfDays 
    AND DATENAME (WEEKDAY, (DATEADD(DD, (RN - 1), @StartDate))) = 'Saturday' 
ORDER BY 
    SaturdayDates DESC

Can you please guide me to achieve my goal? Thanks

Upvotes: 6

Views: 54936

Answers (1)

gofr1
gofr1

Reputation: 15987

Just rewrite it like this table-valued function:

CREATE FUNCTION dbo.Get_NextSaturdayDay()
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    WITH Tens AS
    (
        SELECT 1 N UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 
    ),
    HUNDREDS AS
    (
        SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
    ),
    THOUSANDS AS
    (
        SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
    ),
    Numbers AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS
    )
    SELECT  TOP 1 DATEADD( DD,(RN - 1) , GETDATE() )  as SaturdayDates 
    FROM 
      Numbers
    WHERE 
      RN <= (DATEDIFF(DD,GETDATE() , DATEADD(day,21,GETDATE()) ) + 1) AND DATENAME ( WEEKDAY, (DATEADD( DD,(RN - 1) , GETDATE() )) ) = 'Saturday' 

      ORDER BY SaturdayDates  DESC
)
GO

Than do:

SELECT *
FROM dbo.Get_NextSaturdayDay()

Output:

SaturdayDates
2016-10-15 11:02:33.570

If you need scalar-valued function:

CREATE FUNCTION dbo.Get_NextSaturdayDay ()
RETURNS datetime
AS
BEGIN

    DECLARE @datetime datetime

    ;WITH Tens AS
    (
        SELECT 1 N UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 UNION ALL 
        SELECT 1 
    ),
    HUNDREDS AS
    (
        SELECT T1.N FROM TENS T1 CROSS JOIN TENS T2
    ),
    THOUSANDS AS
    (
        SELECT T1.N FROM HUNDREDS T1 CROSS JOIN HUNDREDS T2
    ),
    Numbers AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM THOUSANDS
    )
    SELECT  TOP 1 @datetime = DATEADD( DD,(RN - 1) , GETDATE() )
    FROM 
      Numbers
    WHERE 
      RN <= (DATEDIFF(DD,GETDATE() , DATEADD(day,21,GETDATE()) ) + 1) AND DATENAME ( WEEKDAY, (DATEADD( DD,(RN - 1) , GETDATE() )) ) = 'Saturday' 

      ORDER BY DATEADD( DD,(RN - 1) , GETDATE() ) DESC
        -- Return the result of the function
        RETURN @datetime

END
GO

Then run:

SELECT  dbo.Get_NextSaturdayDay()

Output:

2016-10-15 11:02:33.570

Upvotes: 4

Related Questions