creating a function to be used in select query

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.isWday = 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.isWday=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.
THanksHi
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.isWday = 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.isWday=1
AND c2.isHoliday=0
)
) as CalcDate
FROM Table
WHERE somedate = @.dte
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.isWday = 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.isWday=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
>|||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.isWday = 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.isWday=1
AND c2.isHoliday=0
))
END
GO
"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.isWday = 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.isWday=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
>