SQL Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

SQL Stored procedure

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2009-04-01 : 16:53:04

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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 17:23:28

If you want to have multiple results, then don't use variables.

Could you show us a data sample to make your issue more clear?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dougancil
Posting Yak Master

217 Posts

Posted - 2009-04-01 : 17:36:23

Tara,

Sure:
The fields are:
SchedName, Firstlisting, oc_start, oc_end, StartOnCallDate, StartOnCallTime, Duration

the data results are below:
CTVS-TRANS KIRKLAND, HUNTER 2009-04-01 07:00:00 2009-04-02 07:00:00 39903 420 1440

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 17:41:36

But what do you want it to show?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dougancil
Posting Yak Master

217 Posts

Posted - 2009-04-01 : 17:56:41

Tara,

What I want it to show is just the SchedName, FirstListing, oc_start and oc_end, but if there are more than one results for the firstlisting, I want them all to show and if theres no data found in firstlisting, it should present 'no o/c listed'

Here's the query I'm running against the SP:
use MDR
GO
declare @return_value int
exec @return_value = [dbo].[oncalladd2]
@subschedule = N'ctvs-trans', <---------this is the schedule name
@lookup_time = null
select 'return value' = @return_value
go

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-04-01 : 17:59:52

You'll need to post the stored procedure code in order for us to help. The information so far is too vague.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

dougancil
Posting Yak Master

217 Posts

Posted - 2009-04-02 : 10:32:04

Tara,

Here is the SP as it is currently written.

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 Oncall_Start,

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

StartOnCallDate,

StartOnCallTime,

Duration

FROM mdr.dbo.mOnCallAdd

WHERE SchedName = @subschedule) Oc

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

AND DATEDIFF(mi, @lookup_time, Oncall_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 OnCall_start

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources