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

SQL Server 2000 Forums

Transact-SQL (2000)

Stored Procedure

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 16:55:33

I am trying to speed up a query that is very tedious and time consuming to run. I thought maybe I could create two stored procedures and then execute the query like this...

Select StayID
From StayTable
Where StayID in (Exec _spSonSceneAfterBooked) and
StayID not in (Exec _spSonScenePriorBooked)

The two stored procedures return a single column of StayID's. I want to use these results from the two stored procedures to filter out or include records. Is this possible? To accomplish this do I need to change something?
Thanks in advance

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 16:58:17

What you have isn't possible. But if you show us the two stored procedures, we can help you out.

This might help you out:

http://www.sqlteam.com/item.asp?ItemID=2368

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:01:09

This is the first one... the second one is identical but the dates are query has different qualifiers on the date range (> or <)
Thanks!!!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE _spSonSceneAfterBooked
@DropDate SmallDateTime = '01/13/2004',
@Mailing nvarchar(20) = 'SonoranScene'
AS
-- Stays that are shared with customers who received the mailing and
-- already had a reservation on the books
Select Distinct Aft.S_StayID
From

(Select Distinct S_StayID
From Stay
Where S_EntDate >@DropDate and
S_Adate >= @DropDate and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID in
(Select Distinct S_ShareID
From Stay
Where S_EntDate >@DropDate and
S_Adate >= @DropDate and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID <> '' and
S_Irecid in
(Select Distinct I_Recid
From MailingHistory
Where Mailing = @Mailing and
DropDate = @DropDate))
Union All

Select Distinct S_StayID
From Stay
Where S_EntDate >@DropDate and
S_Adate >= @DropDate and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID = '' and
S_Irecid in
(Select Distinct I_Recid
From MailingHistory
Where Mailing = @Mailing and
DropDate = @DropDate)) Aft

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 17:04:34

What does the result set look like that the stored procedure produces?

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:06:33

Just a single column of StayID's, these are Nvarchar(20) characters that are generated by the program. There will be 644 of them in a single column. By using the results, I had hoped to select * from the table where the results are not in this stored procedure. Does that make sense???

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 17:09:22

In order to use IN, they need to be comma separated. Take a look at the link that I posted. You'll need to use that method for your IN and NOT IN.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-25 : 17:14:27

You need to learn a little about INNER JOINS -- you should not have all those nested IN() queries.

You have the same queries selecting from themselves and then UNIONing again to the same query but with opposite criteria !

To me your query looks simply like:


Select Distinct Stay.S_StayID
From 
  Stay
inner join
  mailingHistory mh
on
  mh.I_Recid = Stay.S_Irecid		
Where 
  Stay.S_EntDate >@DropDate and
  Stay.S_Adate >= @DropDate and
  Stay.S_Status in ('Resv', 'Hist', 'Canc') and
  mh.mailing = @Mailing AND
  mh.DropDate= @dropDate

does that correspond to what you really want to return? What are you trying to return (in english, NOT in SQL ....)

- Jeff

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:17:22

Sorry to sound stupid... where is that link? I looked on the home page and didn't see anything... ugh... sorry

MBeal

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 17:21:19

The link is in my first reply. I edited my post probably after you read it. It's there now.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:23:07

I'll look at it... thank you for your assistance. You all are great!!!

MBeal

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:26:34

Tara, I looked at the link however I didn't understand how this would help me in this situation. Can you clarify for me please?

MBeal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-25 : 17:28:49

hello !?

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-25 : 17:31:46

Take a look at Jeff's reply.

Tara

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-25 : 17:33:01

Jeff,

Unfortunately, there are individuals in the table that "share" the same room. I need to first identify all of those individuals who share the same room, find their stayID, and then include them as well into the result set. So I am really doing two different things here. Finding first those individuals who recieved a mailing and responded, and then trying to find those individuals who came along with that individual.

I know it looks funny, but that's the best way that I could think of doing it.

MBeal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-25 : 18:11:46

you have two tables here it seems like; tell us what is in those tables.

Then explain exactly what you need to return. Be detailed. From what you said in your last post, I cannot see any way that what you wrote relates to the queries you have provided.

- Jeff

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-26 : 10:00:14

Here we go... follow the bouncing ball...
I have two tables. Table 1 contains records that have a unique ID in them that relates to a customer who received a mailing. I want to identify how many customers who received this mailing actually came and stayed at our hotel -- and I need to identify how many people they brought with them. But first, I must not include those individuals who ALREADY had reservations on the books at the time of the mailing.

The second table contains the stay information about each of the guests. The link between these two tables is a field called I_Recid. Within this second table, there is another unique ID called StayID, which represents the guests reservation. one guest will have the same I_Recid but different StayID's for each stay. There is a third field that is important, called the ShareID, this is a unique field shared between customers who share a room. Two Irecid's for two different customers, two StayID's for those two stays, and the same ShareID between them.

Ok, I initially started with a long query but it took too much time to finish, so I thought I would try a stored procedure and see if I could break the query up into two smaller procedures, but I guess I can't do it.

So I am back to the drawing board... There needs to be two groups -- shares and non-shares. I would first find the customers who received the mailing, then from that list, I need to identify the unique ShareID's of those who were in this result set, then identify ALL of the StayID's from those individuals who shared the same ShareID. Then I will union that result set together with another selection of those who didn't have ShareID's (stayed alone), but did receive the
mailing.

So after thinking about this for a while, I don't know if I have to do another query to identify those who booked before because this query identifies those that booked after anyway. I guess there could be someone who "shared" a room who booked it before the mailing, and then I would only count the one individual who was booked after the mailing... (UGH!!!)

Here's the query...
Select Distinct Aft.S_StayID
From

(Select Distinct S_StayID
From Stay
Where S_EntDate >'01/13/2004' and
S_Adate >= '01/13/2004' and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID in --(grab the shares)
(Select Distinct S_ShareID
From Stay
Where S_EntDate >'01/13/2004' and
S_Adate >= '01/13/2004' and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID <> '' and
S_Irecid in --(In the mailing table)
(Select Distinct I_Recid
From MailingHistory
Where Mailing = 'SonoranScene' and
DropDate = '01/13/2004'))
Union All

Select Distinct S_StayID
From Stay
Where S_EntDate >'01/13/2004' and
S_Adate >= '01/13/2004' and
S_Status in ('Resv', 'Hist', 'Canc') and
S_ShareID = '' and -- (non shares only)
S_Irecid in
(Select Distinct I_Recid
From MailingHistory
Where Mailing = 'SonoranScene' and
DropDate = '01/13/2004')) Aft

Any thoughts?

MBeal

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-26 : 10:12:32

It's your turn to following the bouncing ball ....

fill in the blanks:



CREATE TABLE <table1>  (<col1> <col1type> , <col2> <col2type> , ...)
CREATE TABLE <table2>  (<col1> <col1type> , <col2> <col2type> , ...)
CREATE TABLE <table3>  (<col1> <col1type> , <col2> <col2type> , ...)

GO

-- SAMPLE DATA:

INSERT INTO <table1> (<col1>, <col2>, ....) VALUES (<value1>, <value2>, ....)
....
INSERT INTO <table2> (<col1>, <col2>, ....) VALUES (<value1>, <value2>, ....)
....
INSERT INTO <table3> (<col1>, <col2>, ....) VALUES (<value1>, <value2>, ....)
....

WHAT I WOULD LIKE TO RETURN IS:

<results you are looking for based on the data you have given>

you should be able to take your code, cut and paste it into the query analyzer, and then we can see some sample data and work on the exact SQL for you.

The business scenerio stuff is fine, but try to explain it this way: (this is only an example)

"I wish to return all customers from Table1 who have a mailing in Table2 but NOT in Table3, and ONLY if that customer has a starting date of 1/1/2003 or greater."

something like that ... be clear and exact and concise ....

- Jeff

MBeal
Posting Yak Master

110 Posts

Posted - 2004-03-26 : 11:20:57

Jeff,

Thank you very much for your help up to this point. I have a meeting that I have to go to and I will be tied up all day after that. I won't have time to go into this as much as I would like to right now -- that's frustrating for me.

I think I am going to go at this another way for now and then I'll send you another message later if I continue to have issues with it.

This is the best site anywhere for assistance -- you guys are incredible. Thanks for everything!

MBeal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources