creating a function to be used in select query

16 Dec 2005 8:42 AM

Newbie

I have this select query which returns a date. I would like to be able to
call this from a stored procedure and have the result appear in the result
set from the stored procedure.

i.e. SELECT *, CalcDate
FROM Table
WHERE somedate = @dte

The CalcDate field would be the c.dt in the following select statement.

SELECT c.dt
    FROM dbo.Calendar c
    WHERE
        c.isWeekday = 1
        AND c.isHoliday =0
        AND c.dt > @dte
        AND c.dt <= DATEADD(day, 25, @dte)
    AND 9 = (
        SELECT COUNT(*)
            FROM dbo.Calendar c2
            WHERE c2.dt >= @dte
            AND c2.dt <= c.dt
            AND c2.isWeekday=1
            AND c2.isHoliday=0
    )

How can I do this - I think the above will be a UDF with a return statement
but I am not sure of the syntax.

THanks

16 Dec 2005 8:57 AM

Uri Dimant

Hi
I assume this subquery does not return more than 1 value.Yes you can write
an UDF to return the date as well , so please refer to the BOL for more info

See if this hepls , I could not tested it since you have not provided DDL+
sample data

SELECT *, (SELECT c.dt
    FROM dbo.Calendar c
    WHERE
        c.isWeekday = 1
        AND c.isHoliday =0
        AND c.dt > @dte
        AND c.dt <= DATEADD(day, 25, @dte)
    AND 9 = (
        SELECT COUNT(*)
            FROM dbo.Calendar c2
            WHERE c2.dt >= @dte
            AND c2.dt <= c.dt
            AND c2.isWeekday=1
            AND c2.isHoliday=0
    )
) as CalcDate
FROM Table
WHERE somedate = @dte

Show quote

in message news:uoSoNyhAGHA.3268@TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
>
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @dte
>
> The CalcDate field would be the c.dt in the following select statement.
>
> SELECT c.dt
>    FROM dbo.Calendar c
>    WHERE
>        c.isWeekday = 1
>        AND c.isHoliday =0
>        AND c.dt > @dte
>        AND c.dt <= DATEADD(day, 25, @dte)
>    AND 9 = (
>        SELECT COUNT(*)
>            FROM dbo.Calendar c2
>            WHERE c2.dt >= @dte
>            AND c2.dt <= c.dt
>            AND c2.isWeekday=1
>            AND c2.isHoliday=0
>    )
>
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
>
> THanks
>

16 Dec 2005 9:01 AM

Newbie

I have tried the following but get the error msg:
The column prefix c does not match with a table name . ..

CREATE FUNCTION dbo.AddWorkDays
(
@dte smalldatetime,
@NoDays TINYINT
)

RETURNS SMALLDATETIME
AS
BEGIN
RETURN (SELECT c.dt
    FROM dbo.Calendar c
    WHERE
        c.isWeekday = 1
        AND c.isHoliday =0
        AND c.dt > @dte
        AND c.dt <= DATEADD(day,25, @dte)
    AND @NoDays = (
        SELECT COUNT(*)
            FROM dbo.Calendar c2
            WHERE c2.dt >= @dte
            AND c2.dt <= c.dt
            AND c2.isWeekday=1
            AND c2.isHoliday=0
    ))
END
GO

Show quote

"Newbie" <nospam@noidea.com> wrote in message
news:uoSoNyhAGHA.3268@TK2MSFTNGP10.phx.gbl...
>I have this select query which returns a date. I would like to be able to
>call this from a stored procedure and have the result appear in the result
>set from the stored procedure.
>
> i.e. SELECT *, CalcDate
> FROM Table
> WHERE somedate = @dte
>
> The CalcDate field would be the c.dt in the following select statement.
>
> SELECT c.dt
>    FROM dbo.Calendar c
>    WHERE
>        c.isWeekday = 1
>        AND c.isHoliday =0
>        AND c.dt > @dte
>        AND c.dt <= DATEADD(day, 25, @dte)
>    AND 9 = (
>        SELECT COUNT(*)
>            FROM dbo.Calendar c2
>            WHERE c2.dt >= @dte
>            AND c2.dt <= c.dt
>            AND c2.isWeekday=1
>            AND c2.isHoliday=0
>    )
>
>
> How can I do this - I think the above will be a UDF with a return
> statement but I am not sure of the syntax.
>
> THanks
>