Using Stored Procedures in Select

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

SQL Server 2000 Forums

SQL Server Development (2000)

Using Stored Procedures in Select

Author

Topic

mattdwen
Starting Member

4 Posts

Posted - 2007-01-02 : 19:30:10

I was wondering if it's possible to 'call' a stored procedure from another one, as part of the select.

e.g.
declare @itemid int
select @itemid = item.id, (exec item_checkstatus @itemid) as status
from items

Where 'item_checkstatus' is returning a int value.

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-02 : 19:33:33

That isn't possible. You can do this with user-defined functions though. If you post the code for your sproc, we can tell you if it can convert to a UDF.

Tara Kizer

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-02 : 20:40:36

can if item_checkstatus takes output param.


SET NOCOUNT ON

declare @itemid int
declate @checkstatus int

SELECT @itemid = id
FROM   items

Exec item_checkstatus @itemid, @checkstatus output

SELECT @itemid, @checkstatus
GO

also check BOL for sp_executesql which is probably the way u need to go.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 21:15:53

Ok, we didn't get through...
Post the code for item_checkstatus sproc.

Peter Larsson
Helsingborg, Sweden

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-02 : 22:01:17

Even if the stored procedure has an output parameter or returns it, you still can't execute it in a SELECT statement.

Tara Kizer

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-01-02 : 22:20:44

true, but the desired result set CAN be returned in SP

mattdwen
Starting Member

4 Posts

Posted - 2007-01-02 : 22:32:56

Aha, that kinda makes sense.
The main procedure returns a set of booked items for a specific booking:
CREATE PROCEDURE ScheduleBookingItem_GetByBooking
(@ixBooking Int)
AS

SELECT ScheduleBookingItem.*, !AND THE STATUS!
FROM ScheduleBookingItem
INNER JOIN ScheduleBooking ON ScheduleBookingItem.ixBooking = ScheduleBooking.ixBooking
WHERE ixBooking = @ixBooking AND nStatus = 1 AND fRemoved = 0

The secondary procedure checks to see if the items returned from the first procedure are double booked anywhere, and if so, returns a conflict.
CREATE PROCEDURE ScheduleBookingItem_CheckConflict
(ixItem Int, ixBooking Int, sCategory NVarChar(64), dtmStart DateTime, dtmEnd DateTime)
AS
IF EXISTS (
SELECT ScheduleBookingItem.ixBookingItem
FROM ScheduleBookingItem
INNER JOIN ScheduleBooking ON ScheduleBookingItem.ixBooking = ScheduleBooking.ixOriginal
WHERE ScheduleBookingItem.ixItem = @ixItem
AND ScheduleBookingItem.sCategory = @sCategory
AND ScheduleBookingItem.nStatus = 1
AND ScheduleBookingItem.ixBooking <> @ixBooking
AND (ScheduleBookingItem.intBookingStatus <> -1 OR ScheduleBookingItem.intBookingStatus <> 4)
AND ScheduleBookingItem. fRemoved = 0
AND ScheduleBooking.nStatus = 1
AND ScheduleBooking.fCanceled = 0
AND ScheduleBooking.dtmStart < @dtmEnd
AND ScheduleBooking.dtmEnd >= @dtmStart
)
RETURN -2
ELSE
RETURN (
SELECT intStatus
FROM ScheduleBookingItem
WHERE ixItem = @ixItem
)

So it's a quite a complex check, but I know it works. Basically if it finds something it returns a conflict code, otherwise returns what the code is normally.

All the parameters that get passed to the CheckConflict sproc are available in the initial lookup.

mattdwen
Starting Member

4 Posts

Posted - 2007-01-02 : 22:42:30

I'm guessing by the function comments, if i turn the second sproc into a function, I can just call it like the AVG() function?
I've never written a function before, but i'm guessing it's pretty similar.

mattdwen
Starting Member

4 Posts

Posted - 2007-01-02 : 22:56:42

Yup, I have it sorted thanks. I'll attach the code so you can still point out if there's a better way to do it.

CREATE PROCEDURE ScheduleBookingItem_GetByBooking
(
@ixBooking Int
)
AS
DECLARE @ReturnValue NVarChar(64)
SELECT
ScheduleBookingItem.*,
dbo.ScheduleBookingItem_CheckConflict(ScheduleBookingItem.ixComp, @ixBooking, ScheduleBookingItem.sCategory, ScheduleBooking.dtmStart, ScheduleBooking.dtmEnd) AS Status
FROM ScheduleBookingItem
INNER JOIN ScheduleBooking ON ScheduleBookingItem.ixBooking = ScheduleBooking.ixOriginal
WHERE ScheduleBookingItem.ixBooking = @ixBooking
AND ScheduleBookingItem.nStatus = 1
AND ScheduleBookingItem.fRemoved = 0
AND ScheduleBooking.nStatus = 1

CREATE FUNCTION ScheduleBookingItem_CheckConflict
(
@ixItem Int,
@ixBooking Int,
@sCategory NVarChar(64),
@dtmStart DateTime,
@dtmEnd DateTime
)
RETURNS NVarChar(64)
AS
BEGIN
DECLARE @sStatus NVarChar(64)
IF EXISTS (
SELECT ScheduleBookingItem.ixBookingItem
FROM ScheduleBookingItem
INNER JOIN ScheduleBooking ON ScheduleBookingItem.ixBooking = ScheduleBooking.ixOriginal
WHERE ScheduleBookingItem.ixComp = @ixItem
AND ScheduleBookingItem.sCategory = @sCategory
AND ScheduleBookingItem.nStatus = 1
AND ScheduleBookingItem.ixBooking <> @ixBooking
AND (ScheduleBookingItem.intBookingStatus <> -1 OR ScheduleBookingItem.intBookingStatus <> 4)
AND ScheduleBookingItem. fRemoved = 0
AND ScheduleBooking.nStatus = 1
AND ScheduleBooking.fCanceled = 0
AND ScheduleBooking.dtmStart < @dtmEnd
AND ScheduleBooking.dtmEnd >= @dtmStart
)
SET @sStatus = 'Conflict'
ELSE
BEGIN
SELECT @sStatus = sStatus
FROM ScheduleBookingItem
INNER JOIN ScheduleStatus ON ScheduleBookingItem.intBookingStatus = ScheduleStatus.intCode
WHERE ixComp = @ixItem
AND nStatus = 1
END

RETURN @sStatus
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources