select string rows

microsoft.public.sqlserver.server

17 May 2005 7:04 PM

Nathan Carroll

What am I missing in the or for the return to work here?

ALTER PROCEDURE dbo.StoredProcedure2

@user int,
@sDate DATETIME,
@fDate DATETIME

AS

CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255), UserHours
float(8))

INSERT INTO temptable (client, WO, UserHours)
(SELECT dbo.BillableTimesheet.FullClient as client,
dbo.BillableTimesheet.WO as WO,
SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
FROM dbo.BillableTimesheet
WHERE (dbo.BillableTimesheet.Userid = @user) AND
(dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate, 102)
AND CONVERT(DATETIME, @fDate, 102))
GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)

RETURN SELECT SUM(dbo.BillableTimesheet.BillableHours) AS allhours,
temptable.client, temptable.UserHours, temptable.WO
FROM dbo.BillableTimesheet INNER JOIN temptable ON
dbo.BillableTimesheet.FullClient = temptable.client
WHERE (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
102) AND CONVERT(DATETIME, @fDate, 102))
GROUP BY dbo.BillableTimesheet.FullClient, temptable.client,
temptable.UserHours, temptable.WO

17 May 2005 7:16 PM

Jens Süßmeyer

If you don´t get an exception in here (seems that i should be ok), you have
to return the resultset via

Select client, WO, UserHours from temptable

--
HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

Show quoteHide quote

"Nathan Carroll" <nath_an@thelosth_orizon.com> schrieb im Newsbeitrag
news:uRsFCQxWFHA.3348@TK2MSFTNGP14.phx.gbl...
> What am I missing in the or for the return to work here?
>
>
> ALTER PROCEDURE dbo.StoredProcedure2
>
> @user int,
> @sDate DATETIME,
> @fDate DATETIME
>
> AS
>
> CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255), UserHours
> float(8))
>
> INSERT INTO temptable (client, WO, UserHours)
> (SELECT dbo.BillableTimesheet.FullClient as client,
> dbo.BillableTimesheet.WO as WO,
> SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> FROM dbo.BillableTimesheet
> WHERE (dbo.BillableTimesheet.Userid = @user) AND
> (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate, 102)
> AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
>
> RETURN SELECT SUM(dbo.BillableTimesheet.BillableHours) AS allhours,
> temptable.client, temptable.UserHours, temptable.WO
> FROM dbo.BillableTimesheet INNER JOIN temptable ON
> dbo.BillableTimesheet.FullClient = temptable.client
> WHERE (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME,
> @sDate,
> 102) AND CONVERT(DATETIME, @fDate, 102))
> GROUP BY dbo.BillableTimesheet.FullClient, temptable.client,
> temptable.UserHours, temptable.WO
>
>

Are all your drivers up to date? click for free checkup

17 May 2005 9:06 PM

Nathan Carroll

if I change to what you select statement below i get:

Running dbo."StoredProcedure2" ( @user = 7, @sDate = 1/1/2005, @fDate =
2/1/2005 ).
There is already an object named 'temptable' in the database.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running dbo."StoredProcedure2".

Same results if I use the previous return select statement

Show quoteHide quote

"Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uKYT5TxWFHA.712@TK2MSFTNGP14.phx.gbl...
> If you don´t get an exception in here (seems that i should be ok), you
have
> to return the resultset via
>
> Select client, WO, UserHours from temptable
>
> --
> HTH, Jens Suessmeyer.
>
> ---
> http://www.sqlserver2005.de
> ---
> "Nathan Carroll" <nath_an@thelosth_orizon.com> schrieb im Newsbeitrag
> news:uRsFCQxWFHA.3348@TK2MSFTNGP14.phx.gbl...
> > What am I missing in the or for the return to work here?
> >
> >
> > ALTER PROCEDURE dbo.StoredProcedure2
> >
> > @user int,
> > @sDate DATETIME,
> > @fDate DATETIME
> >
> > AS
> >
> > CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255), UserHours
> > float(8))
> >
> > INSERT INTO temptable (client, WO, UserHours)
> > (SELECT dbo.BillableTimesheet.FullClient as client,
> > dbo.BillableTimesheet.WO as WO,
> > SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> > FROM dbo.BillableTimesheet
> > WHERE (dbo.BillableTimesheet.Userid = @user) AND
> > (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
102)
> > AND CONVERT(DATETIME, @fDate, 102))
> > GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
> >
> > RETURN SELECT SUM(dbo.BillableTimesheet.BillableHours) AS allhours,
> > temptable.client, temptable.UserHours, temptable.WO
> > FROM dbo.BillableTimesheet INNER JOIN temptable ON
> > dbo.BillableTimesheet.FullClient = temptable.client
> > WHERE (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME,
> > @sDate,
> > 102) AND CONVERT(DATETIME, @fDate, 102))
> > GROUP BY dbo.BillableTimesheet.FullClient, temptable.client,
> > temptable.UserHours, temptable.WO
> >
> >
>
>

17 May 2005 9:40 PM

Nathan Carroll

sorry this is what I get forgot the drop table

Running dbo."StoredProcedure2" ( @user = 7, @sDate = 1/1/2005, @fDate =
2/1/2005 ).
(18 row(s) affected)
No more results.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running dbo."StoredProcedure2".

Show quoteHide quote

"Nathan Carroll" <nath_an@thelosth_orizon.com> wrote in message
news:OTSdiUyWFHA.3076@TK2MSFTNGP12.phx.gbl...
> if I change to what you select statement below i get:
>
> Running dbo."StoredProcedure2" ( @user = 7, @sDate = 1/1/2005, @fDate =
> 2/1/2005 ).
> There is already an object named 'temptable' in the database.
> No rows affected.
> (0 row(s) returned)
> @RETURN_VALUE =
> Finished running dbo."StoredProcedure2".
>
> Same results if I use the previous return select statement
>
>
>
> "Jens Süßmeyer" <Jens@Remove_this_For_Contacting.sqlserver2005.de> wrote
in
> message news:uKYT5TxWFHA.712@TK2MSFTNGP14.phx.gbl...
> > If you don´t get an exception in here (seems that i should be ok), you
> have
> > to return the resultset via
> >
> > Select client, WO, UserHours from temptable
> >
> > --
> > HTH, Jens Suessmeyer.
> >
> > ---
> > http://www.sqlserver2005.de
> > ---
> > "Nathan Carroll" <nath_an@thelosth_orizon.com> schrieb im Newsbeitrag
> > news:uRsFCQxWFHA.3348@TK2MSFTNGP14.phx.gbl...
> > > What am I missing in the or for the return to work here?
> > >
> > >
> > > ALTER PROCEDURE dbo.StoredProcedure2
> > >
> > > @user int,
> > > @sDate DATETIME,
> > > @fDate DATETIME
> > >
> > > AS
> > >
> > > CREATE TABLE temptable (client nvarchar(50), WO nvarchar(255),
UserHours
> > > float(8))
> > >
> > > INSERT INTO temptable (client, WO, UserHours)
> > > (SELECT dbo.BillableTimesheet.FullClient as client,
> > > dbo.BillableTimesheet.WO as WO,
> > > SUM(dbo.BillableTimesheet.BillableHours) AS UserHours
> > > FROM dbo.BillableTimesheet
> > > WHERE (dbo.BillableTimesheet.Userid = @user) AND
> > > (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME, @sDate,
> 102)
> > > AND CONVERT(DATETIME, @fDate, 102))
> > > GROUP BY dbo.BillableTimesheet.FullClient, dbo.BillableTimesheet.WO)
> > >
> > > RETURN SELECT SUM(dbo.BillableTimesheet.BillableHours) AS allhours,
> > > temptable.client, temptable.UserHours, temptable.WO
> > > FROM dbo.BillableTimesheet INNER JOIN temptable ON
> > > dbo.BillableTimesheet.FullClient = temptable.client
> > > WHERE (dbo.BillableTimesheet.BillableDate BETWEEN CONVERT(DATETIME,
> > > @sDate,
> > > 102) AND CONVERT(DATETIME, @fDate, 102))
> > > GROUP BY dbo.BillableTimesheet.FullClient, temptable.client,
> > > temptable.UserHours, temptable.WO
> > >
> > >
> >
> >
>
>