Newbie Question - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Newbie Question

Author

Topic

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-01 : 01:41:11

Hi all,

I have a table that contains the details about a "booking" and another child table that contains the items booked. I created a stored procedure that accepts the data for the booking table and the data for the child table as a comma-separated parameter because it seemed logical to pass both the data to a single SP. Using a split function I split the data and store it in the child table. The problem is I don't know how to retreive the data from both the tables using one SP. This is the SP I've written to retrieve the data but it doesn't work.

---------------------------------------------------------------------

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE xsp_SP
@ID int OUT,
@List varchar(200) OUT,
@ErrorCode int OUT
AS
SET NOCOUNT ON
SELECT cola, colb,... FROM table WHERE ID=@ID
SELECT @ErrorCode = @@Error

DECLARE Cursor CURSOR
READ_ONLY
FOR SELECT Item,Quantity FROM table WHERE ID=@ID

Declare @Item varchar(50)
Declare @Quantity varchar(2)

OPEN Cursor

FETCH NEXT FROM Cursor INTO @Item,@Quantity
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SELECT @List=COALESCE(@List,'') + ',' + COALESCE(@Item,'') + ',' + COALESCE(@Quantity,'')
END
FETCH NEXT FROM CursorINTO @Item,@Quantity
END
CLOSE Cursor
DEALLOCATE Cursor
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

---------------------------------------------------------------------

I know that this is not the way to do it but I don't know the right way. Can someone help?

Adi

-------------------------
/me sux @sql server

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-01 : 05:43:20

adi, you don't need a cursor here! Look at the following thread and see byrmol's solution. Also look at the article to get another perspective on the solution.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19647

OS

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-01 : 06:29:36

Thanks!

Adi

-------------------------
/me sux @sql server

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-01 : 08:11:28

Hi Mohd,

It's all greek and latin to me!

Adi

-------------------------
/me sux @sql server

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-01 : 09:16:50

You are not thinking hard enough then! Ok, let me make an assumption here, you only want to retreive data from the Item/Quantity table for one ID at a time. Here is a similar example of how you can retreive all titles and their prices for a particular publisher:


declare @pubid int
declare @str varchar(8000)

set @pubid = 0736

select @str = coalesce(@str + ', ',  '') + title + ';' + cast(price as varchar(20)) from titles where pub_id = @pubid
select pub_name, @str from publishers where pub_id = @pubid

You should be able to adapt the example above to your requirements. Remember this only works for one publisher at a time. If you want to retreive, say, a list of publishers and all their related titles then you'd have to follow the UDF example byrmol has posted in the thread above.

OS

t1g312
Posting Yak Master

148 Posts

Posted - 2004-08-01 : 09:22:02

Hi Mohd,

I got it working before I saw your post :). Thanks anyway!

Adi

-------------------------
/me sux @sql server

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources