SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Problem with Returned Recordset
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RoLYroLLs
Constraint Violating Yak Guru

USA
255 Posts

Posted - 04/16/2004 :  01:27:11  Show Profile  Visit RoLYroLLs's Homepage  Reply with Quote
Hey gals and guys! Here's a question for all:

I wrote the following sproc:

CREATE procedure rolyrolls.proc_Search
	@TypeID int, 
	@MakeID int, 
	@YearFrom smallint, 
	@YearTo smallint, 
	@MilesFrom int, 
	@MilesTo int, 
	@ZIP nchar(5), 
	@Mile nchar(5)
as
	DECLARE @ZIPCode nchar(5)
	SET @ZIPCode = @ZIP
	
	DECLARE @Miles REAL
	SET @Miles = @Mile
	
	DECLARE @RowCount int
	SELECT @RowCount = Count(*) FROM ZipCodes.dbo.ZIPCodes WHERE ZIPCode = @ZIPCode AND CityType = 'D'
	
	if @RowCount > 0
begin
	SELECT Color.Color AS [Int], Color_1.Color AS Ext, DealershipModel.*, Model.*, Make.*, Type.*, Condition.*, 
Dealership.*, MakeType.*, ClassInvoiceDetail.*, ClassInvoice.*, ClassTypePrice.*, ClassType.*, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
	FROM Type INNER JOIN ((MakeType INNER JOIN (Make INNER JOIN Model ON Make.MakeID = Model.MakeID) ON MakeType.MakeTypeID 
= Make.MakeTypeID) INNER JOIN (Dealership INNER JOIN (Condition INNER JOIN (Color INNER JOIN ((ClassType INNER JOIN 
ClassTypePrice ON ClassType.ClassTypeID = ClassTypePrice.ClassTypeID) INNER JOIN ((ClassInvoice INNER JOIN ClassInvoiceDetail ON 
ClassInvoice.ClassInvoiceID = ClassInvoiceDetail.ClassInvoiceID) INNER JOIN (DealershipModel INNER JOIN Color AS Color_1 ON 
DealershipModel.ExtColorID = Color_1.ColorID) ON ClassInvoiceDetail.ClassInvoiceDetailID = DealershipModel.ClassInvoiceDetailID) 
ON ClassTypePrice.ClassTypePriceID = ClassInvoiceDetail.ClassTypePriceID) ON Color.ColorID = DealershipModel.IntColorID) ON 
Condition.ConditionID = DealershipModel.ConditionID) ON Dealership.DealershipID = ClassInvoice.DealershipID) ON Model.ModelID = 
DealershipModel.ModelID) ON Type.TypeID = DealershipModel.TypeID INNER JOIN zipcodes.dbo.ZipCodes z ON 
Dealership.DealershipZipCode = z.ZIPCode, ZipCodes.dbo.RadiusAssistant(@ZIPCode,@Miles) r
	WHERE ((((Model.MakeID)=@MakeID) OR ((Type.TypeID)=@TypeID) OR (((DealershipModel.DealershipModelYear) Between @YearFrom 
And @YearTo) AND ((DealershipModel.Mileage) Between @MilesFrom And @MilesTo) AND ((Model.MakeID)=@MakeID))) AND 
((DealershipModel.DealershipModelAvailable)=1) AND ((DealershipModel.DealershipModelDeleted)=0) AND 
((Dealership.DealershipActive)=1) AND ((Dealership.DealershipApproved)=1) AND ((MakeType.MakeType)='normal') AND 
((ClassInvoiceDetail.ClassInvoiceDetailExpired)=0) AND ((ClassInvoice.ClassInvoiceVoid)=0) AND 
((DealershipModel.DealershipModelRenew)=0) AND ((ClassInvoiceDetail.ClassInvoiceDetailPaymentDue)=0) AND z.Latitude <= r.MaxLat 
AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND CityType = 'D' AND ZIPCodeType <> 'M' 
AND ZipCodes.dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles)
	ORDER BY Distance , DealershipModel.DealershipModelYear DESC , DealershipModel.Mileage;
end
GO


I ran the sp on QA and it returned the proper results and data. I tested it on my dev server and web page, and ran great. When I transfered this to the prod server, I found that half the data was missing, even when i ran the sp on the prod on QA. I found that by changing the SELCT line to this:

SELECT *, Color.Color AS [Int], Color_1.Color AS Ext, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance


I got more of the data but was still missing one of the colors (the 'ext' one). Then I changed it to:

SELECT Color.Color AS [Int], Color_1.Color AS Ext, ZipCodes.dbo.DistanceAssistant
(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance, *


and got everything I needed. I went thru every sp, over 230 of them, and made modifications to make sure th '*' part at the end. Now I find that I do not get all the data i need for a few other sp's.

Anyone still with me? Maybe there is a problem with returning too many fields? Or is there something else I'm not aware of? Remember it all works fine at the home dev server. Any input would be great!

Thanks to all!


- RoLY roLLs

Edited by - RoLYroLLs on 04/16/2004 16:28:28

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 04/16/2004 :  02:57:28  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
This is a great example of why you should never, ever, ever ,ever, ever, ever, ever use '*' in a query.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

USA
255 Posts

Posted - 04/16/2004 :  12:30:45  Show Profile  Visit RoLYroLLs's Homepage  Reply with Quote
Sounds good to me. But how about some detailed info on this.

Let's say on the example above I have about 50, 60 or say 100 (for discussion purposes) fields returned and need every single one. Must I type out each field? Do problems arise after a certain amount of fields, or a certain amount of data?

I've been converting my db from access to sql so my sprocs are not optimized. I'm just making sure that everything still works the way they should. I used '*' beause as I was designing, there were times clients wanted a new field to add which needed to be returned in several queries so it eliminated me having to go the queries to change them. I realize I may get data I won't need or use, but with 230+ queries it was a hassle.

Thanks.

- RoLY roLLs
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 04/16/2004 :  13:23:47  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
The problem will only on number of records if you do use '*'. The query is going to have to figure out what fields are in these tables. This can cause a large amount of contention in your system tables and slow the entire system down.

You "want" to have to changet the sprocs when you change the structure underneath. I know this seems like a pain, but it will stop the very thing you have occurring now. There is not "variable" in the picture. If you have different structures in different environments, they query will still return the same results. You will then go through a progression of dev/test/prod to see the changes promote through your environments.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

RoLYroLLs
Constraint Violating Yak Guru

USA
255 Posts

Posted - 04/16/2004 :  16:30:16  Show Profile  Visit RoLYroLLs's Homepage  Reply with Quote
Thank you. I'll have to work on that. I also fixed this post to prevent sideways-scrolling, sorry for that.

- RoLY roLLs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000