Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure

Status
Not open for further replies.
dougancil

dougancil

IS-IT--Management
Mar 31, 2009
44
US
Everyone,

I'm trying to run a stored procedure to give me more than a single result using the following line:

SELECT TOP 1 @FirstListing = FirstListing, @Comment = Comment, @oc_start = oc_start, @oc_end = oc_end

Granted there is much more to the stored procedure than this but this is a snip of it. What I want to do is to produce more than a single result to show on a web page. Currently I'm only getting one result being presented. I am more than willing to display the rest of the code if anyone may be able to help.

Thanks
 
Sort by date Sort by votes
Just don't use TOP 1 and use proper WHERE clause to generate desired resultset:
Code: 
SELECT FirstListing, 
       Comment, 
       oc_start,
       oc_end
FROM ?????????????????
WHERE ????????????????????????????

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Ok so here's the stored procedure as it is currently written:

CREATE PROCEDURE [dbo].[oncalladd]

@subschedule varchar(50),

@lookup_time smalldatetime = NULL,

@no_oc_value varchar(50) = 'No o/c listed'

AS BEGIN

IF (@lookup_time IS NULL)

SET @lookup_time = CURRENT_TIMESTAMP

DECLARE @FirstListing varchar(50)

DECLARE @Comment varchar(20)

DECLARE @oc_start smalldatetime

DECLARE @oc_end smalldatetime

SELECT @FirstListing = FirstListing, @Comment = Comment, @oc_start = oc_start, @oc_end = oc_end

FROM (SELECT Rank, SchedName, FirstListing, Comment,

CONVERT(smalldatetime, DATEADD(mi, StartOnCallTime, CONVERT(smalldatetime, StartOnCallDate - 1))) AS oc_start,

CONVERT(smalldatetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(smalldatetime, StartOnCallDate - 1))) AS oc_end,

StartOnCallDate, StartOnCallTime, Duration, AddDate, AddTime, MainSchedName

FROM mdr.dbo.mOnCallAdd

WHERE SchedName = @subschedule) oc

WHERE DATEDIFF(mi, oc_start, @lookup_time) >= 0

AND DATEDIFF(mi, @lookup_time, oc_end) > 0

AND NOT EXISTS (SELECT *

FROM mdr.dbo.mOnCallDelete del_oc

WHERE oc.SchedName = del_oc.SchedName

AND oc.FirstListing = del_oc.FirstListing

AND oc.Rank = del_oc.Rank

AND oc.StartOnCallDate = del_oc.StartOnCallDate

AND oc.StartOnCallTime = del_oc.StartOnCallTime

AND oc.Duration = del_oc.Duration

AND oc.AddDate = del_oc.AddDate

AND oc.AddTime = del_oc.AddTime)

ORDER BY Rank, oc_start

SELECT

CASE WHEN @FirstListing IS NULL THEN @no_oc_value ELSE @FirstListing END AS oncall,

@Comment AS comment,

@oc_start AS oc_start,

@oc_end AS oc_end

END
GO
 
Upvote 0 Downvote
NOT TESTED AT ALL!!!!

Code: 
[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[oncalladd]
       @subschedule [COLOR=blue]varchar[/color](50),
       @lookup_time [COLOR=blue]smalldatetime[/color] = NULL,
       @no_oc_value [COLOR=blue]varchar[/color](50) = [COLOR=red]'No o/c listed'[/color]

[COLOR=blue]AS[/color] 
  [COLOR=blue]BEGIN[/color]
   [COLOR=blue]IF[/color] (@lookup_time [COLOR=blue]IS[/color] NULL)
      [COLOR=blue]SET[/color] @lookup_time = [COLOR=#FF00FF]CURRENT_TIMESTAMP[/color]

    [COLOR=blue]SELECT[/color] Rank,
           SchedName,
           [COLOR=#FF00FF]COALESCE[/color](FirstListing,@no_oc_value) [COLOR=blue]AS[/color] FirstListing,
           [COLOR=blue]Comment[/color],
           [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], [COLOR=#FF00FF]DATEADD[/color](mi, StartOnCallTime, [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], StartOnCallDate - 1))) [COLOR=blue]AS[/color] oc_start,
           [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], [COLOR=#FF00FF]DATEADD[/color](mi, StartOnCallTime + Duration, [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], StartOnCallDate - 1))) [COLOR=blue]AS[/color] oc_end,
           StartOnCallDate,
           StartOnCallTime,
           Duration,
           AddDate,
           AddTime,
           MainSchedName
    [COLOR=blue]FROM[/color] mdr.dbo.mOnCallAdd
    [COLOR=blue]WHERE[/color] SchedName = @subschedule
      AND [COLOR=#FF00FF]DATEDIFF[/color](mi, oc_start, @lookup_time) >= 0
      AND [COLOR=#FF00FF]DATEDIFF[/color](mi, @lookup_time, oc_end)   >  0
      AND NOT EXISTS ([COLOR=blue]SELECT[/color] 1
                            [COLOR=blue]FROM[/color] mdr.dbo.mOnCallDelete del_oc
                      [COLOR=blue]WHERE[/color] oc.SchedName = del_oc.SchedName
                        AND oc.FirstListing = del_oc.FirstListing
                        AND oc.Rank = del_oc.Rank
                        AND oc.StartOnCallDate = del_oc.StartOnCallDate
                        AND oc.StartOnCallTime = del_oc.StartOnCallTime
                        AND oc.Duration = del_oc.Duration
                        AND oc.AddDate = del_oc.AddDate
                        AND oc.AddTime = del_oc.AddTime)
    [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Rank, oc_start
[COLOR=blue]END[/color]

Just removed the variable assign.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
BBorissov,

I tried what you had written and I'm getting a syntax error near Coalesce. I should mention that I'm using SQL Server 2003.

Coalesce (firstlisting, @no_oc_value) AS firstlisting, Comment,
 
Upvote 0 Downvote
There is no such version of SQL Server :)
COALESCE() function exists in all versions of SQL Server, I know - 2000, 2005 and 2008.

BTW becuase I removed the derived table there is no such alias as OC anymore, so change this line:
Code: 
 FROM mdr.dbo.mOnCallAdd

to

Code: 
 FROM mdr.dbo.mOnCallAdd oc

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Ok Borislav,

I changed the line as you suggested. Here is the stored procedure as its written now:

CREATE PROCEDURE [dbo].[oncalladd2]

@subschedule varchar(50),

@lookup_time smalldatetime = NULL,

@no_oc_value varchar(50) = 'No o/c listed'

AS BEGIN

IF (@lookup_time IS NULL)

SET @lookup_time = CURRENT_TIMESTAMP

Select Rank,
Schedname,
Coalesce (firstlisting, @no_oc_value) AS firstlisting, Comment,
Convert (smalldatetime, DATEADD(mi, StartOnCallTime,
Convert (smalldatetime, StartOnCallDate -1))) as oc_start,
Convert (smalldatetime, DATEADD(mi, StartOnCallTime + Duration,
Convert(smalldatetime, StartOnCallDate -1))) AS oc_end,
StartOnCallDate,
StartOnCallTime,
Duration,
AddDate,
AddTime,
MainSchedName
FROM mdr.dbo.mOnCallAdd oc
Where SchedName = @subschedule
And DATEDIFF(mi,oc_start, @lookup_time) >=0
And DATEDIFF(mi, @lookup_time, oc_end) >0
and not exists (select 1 from mdr.dbo.mOnCallDelete del_oc
Where oc.schedname = del_oc.schedname
and oc.FirstListing = del_oc.firstlisting
and oc.rank = del_oc.rank
and oc.startoncalldate = del_oc.startoncalldate
and oc.startoncalltime = del_oc.startoncalltime
and oc.duration = del_oc.duration
and oc.adddate = del_oc.adddate
and oc.addtime = del_oc.addtime)
order by rank, oc_start
End

Now I'm getting the following error:
Invalid column name 'oc_start'
Invalid column name 'oc_end'
 
Upvote 0 Downvote
I'm so tyred right know to think how to remove derived table, here it back :)

Code: 
[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] [dbo].[oncalladd]
       @subschedule [COLOR=blue]varchar[/color](50),
       @lookup_time [COLOR=blue]smalldatetime[/color] = NULL,
       @no_oc_value [COLOR=blue]varchar[/color](50) = [COLOR=red]'No o/c listed'[/color]

[COLOR=blue]AS[/color]
  [COLOR=blue]BEGIN[/color]
   [COLOR=blue]IF[/color] (@lookup_time [COLOR=blue]IS[/color] NULL)
      [COLOR=blue]SET[/color] @lookup_time = [COLOR=#FF00FF]CURRENT_TIMESTAMP[/color]

   [COLOR=blue]SELECT[/color] * 
       [COLOR=blue]FROM[/color]([COLOR=blue]SELECT[/color] (Rank,
                    SchedName,
                    [COLOR=#FF00FF]COALESCE[/color](FirstListing,@no_oc_value) [COLOR=blue]AS[/color] FirstListing,
                    [COLOR=blue]Comment[/color],
                    [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], [COLOR=#FF00FF]DATEADD[/color](mi, StartOnCallTime, [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], StartOnCallDate - 1))) [COLOR=blue]AS[/color] oc_start,
                    [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], [COLOR=#FF00FF]DATEADD[/color](mi, StartOnCallTime + Duration, [COLOR=#FF00FF]CONVERT[/color]([COLOR=blue]smalldatetime[/color], StartOnCallDate - 1))) [COLOR=blue]AS[/color] oc_end,
                    StartOnCallDate,
                    StartOnCallTime,
                    Duration,
                    AddDate,
                    AddTime,
                    MainSchedName
             [COLOR=blue]FROM[/color] mdr.dbo.mOnCallAdd
                  [COLOR=blue]WHERE[/color] SchedName = @subschedule) Oc
   [COLOR=blue]WHERE[/color]  [COLOR=#FF00FF]DATEDIFF[/color](mi, oc_start, @lookup_time) >= 0
      AND [COLOR=#FF00FF]DATEDIFF[/color](mi, @lookup_time, oc_end)   >  0
      AND NOT EXISTS ([COLOR=blue]SELECT[/color] 1
                            [COLOR=blue]FROM[/color] mdr.dbo.mOnCallDelete del_oc
                      [COLOR=blue]WHERE[/color] oc.SchedName = del_oc.SchedName
                        AND oc.FirstListing = del_oc.FirstListing
                        AND oc.Rank = del_oc.Rank
                        AND oc.StartOnCallDate = del_oc.StartOnCallDate
                        AND oc.StartOnCallTime = del_oc.StartOnCallTime
                        AND oc.Duration = del_oc.Duration
                        AND oc.AddDate = del_oc.AddDate
                        AND oc.AddTime = del_oc.AddTime)
    [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] Rank, oc_start
[COLOR=blue]END[/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Borislav,

Thank you for your help on this so far, you're educating me on how to write stored procedures better. I rewrote the query to reflect what you have here and I'm getting a syntax error near

Select *
from (Select (Rank,
Schedname,

Telling me that its the ",
 
Upvote 0 Downvote
Code: 
SELECT (Rank,
should be
Code: 
SELECT Rank,
w/o bracket in front of Rank

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Borislav,

Ok so I've appended your script as follows:

ALTER PROCEDURE [dbo].[oncalladd2]
@subschedule varchar(50),
@lookup_time smalldatetime = NULL,
@no_oc_value varchar(50) = 'No o/c listed'

AS
BEGIN
IF (@lookup_time IS NULL)
SET @lookup_time = CURRENT_TIMESTAMP

SELECT *
FROM(Select SchedName,
COALESCE(FirstListing,@no_oc_value) AS FirstListing,
CONVERT(smalldatetime, DATEADD(mi, StartOnCallTime, CONVERT(smalldatetime, StartOnCallDate - 1))) AS oc_start,
CONVERT(smalldatetime, DATEADD(mi, StartOnCallTime + Duration, CONVERT(smalldatetime, StartOnCallDate - 1))) AS oc_end,
StartOnCallDate,
StartOnCallTime,
Duration
FROM mdr.dbo.mOnCallAdd
WHERE SchedName = @subschedule) Oc
WHERE DATEDIFF(mi, oc_start, @lookup_time) >= 0
AND DATEDIFF(mi, @lookup_time, oc_end) > 0
AND NOT EXISTS (SELECT 1
FROM mdr.dbo.mOnCallDelete del_oc
WHERE oc.SchedName = del_oc.SchedName
AND oc.FirstListing = del_oc.FirstListing
AND oc.StartOnCallDate = del_oc.StartOnCallDate
AND oc.StartOnCallTime = del_oc.StartOnCallTime
AND oc.Duration = del_oc.Duration)
ORDER BY oc_start
END

What its still showing me is StartOncallDate, StartOncalltime and Duration. I dont need to see these fields. I just need to see the results of the query as to who is oncall for any particular time. How would I have to modify what I have to make that work?
 
Upvote 0 Downvote
Just use field list and put there only these fields you need in the main select. Do not use SELECT * but

SELECT SchedName[, put here others if you need them]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Borislav,

I'm running this query against the stored procedure in a table that has no entries and its not returning a value back to me of 'No o/c listed' as it should.

use MDR
declare @return_value int
exec @return_value = [dbo].[oncalladd2]
@subschedule = N'arcim',
@lookup_time = null
select 'return value' = @return_value

Any idea why?
 
Upvote 0 Downvote
No,
I didn't know your data not even the Database design.
Could you create an example that could reproduce this behavior?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
I'm not sure what you're asking. The query that is posted in my last post returns no records but doesnt show me that there is no o/c listed as per line 4. With that query, I know that the schedule has no entries in it but I get a blank result back and nothing else.
 
Upvote 0 Downvote
OK, I have NO idea how your data looks like.
I have NO idea what records you have in tables.
I CAN'T test that SP here, so prepare an example that could reproduce the error behavior:
Code: 
CREATE DATABASE TestMe
GO
USE TestMe
CREATE TABLE BlahBlah1 (field list....)
CREATE TABLE BlahBlah2 (field list....)
...
INSERT INTO BlahBlah1 VALUES (some values that match field list)
INSERT INTO BlahBlah1 VALUES (some values that match field list)
INSERT INTO BlahBlah1 VALUES (some values that match field list)
INSERT INTO BlahBlah1 VALUES (some values that match field list)

.... ---and do this for ALL example tables
GO
declare @return_value int
exec @return_value = [dbo].[oncalladd2]
@subschedule = N'arcim',
@lookup_time = null
select 'return value' = @return_value
I have the code of the SP already :)


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Borislav,

So my fields are as follows:
ID
Rec ID
Timestamp
Schedname
Rank
StartOnCallDate
StartOnCallTime
Override
FirstListing
Duration
TimeDifference
AddDate
AddTime
Initials
OpName
Comment

This is for an on call schedule for physicians that we track in a database. What we need to know is on any given day, who is on call for any given schedule, and if there is more than one doctor on call, present all of those values to us, and if no one is on call, we need a result of 'No o/c listed.' We dont need to know StartOnCallDate and Time, but we still need the oc_start and oc_end field to be presented. I hope that makes more sense.
 
Upvote 0 Downvote
I'm almost give up.
I want DATA not fields. I want an example of your data from ALL tables that are involved in that SP.
And the data should reproduce error in that SP.
I don't need ALL of your data, but example one and please tell me what is the expected result from the data you posted.
As one of the member of another forum says: My Chrystal ball don't work today :)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Borislav,

Here's some data that produced a result,

SchedName FirstListing Oncall_Start OnCall_end StartOnCallDate StartOnCallTime Duration
CTVS-CARDIO KIRKLAND, HUNTER 4/1/2009 7:00:00 AM 4/2/2009 7:00:00 AM 39903 420 1440

Now if I run use MDR
declare @return_value int
exec @return_value = [dbo].[oncalladd2]
@subschedule = N'arcim', <-------------ARCIM is the schedule name
@lookup_time = null
select 'return value' = @return_value

I get nothing back as a result of that query, not even a 'No o/c listed' and the query shows that its an empty data set.
 
Upvote 0 Downvote
But you don't have 'arcim' in the table?
Try with:
Code: 
declare @return_value int
exec @return_value = [dbo].[oncalladd2]
                   @subschedule = N'CTVS-CARDIO',
                   @lookup_time = null
select 'return value' = @return_value
[code]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
514
Andrzejek
Andrzejek
Auguy
  • Locked
  • Question
Passing TVP instead of Individual Parameters to Procedure
Replies
0
Views
173
Auguy
Auguy
ghiehotamares
  • Locked
  • Question
How to call stored procedure to display records in fron-end data entry
Replies
1
Views
192
fredericofonseca
fredericofonseca
BeatriceC
  • Locked
  • Question
SQL Linked server StoredProcefure job creates temp files
Replies
1
Views
208
pjw001
pjw001
AlphaMann
  • Locked
  • Question
Stored Procedure
Replies
1
Views
146
simian336
simian336

Part and Inventory Search

Sponsor

Back
Top