July 7, 2004 at 11:05 am
I was made some changes to my database schema today, and was going through and updaing my stored procedures. I noticed that one of them was using (gasp) dynamic SQL. Since I'm updating my stored procedures anyway, I'd like to fix it, but I really don't know how. Here's the code; I'll comment on why I can't figure something better out below it.
CREATE PROCEDURE dbo.GetAirportsNear @latitude FLOAT,
@longitude FLOAT,
@maxDistance INT,
@maxToReturn INT,
@country VARCHAR(50),
@minRunwayLength INT AS
SELECT id, dbo.DistanceBetween(@latitude, @longitude, dbo.DMSToRadians(latd, latm, lats, lath), dbo.DMSToRadians(lond, lonm, lons, lonh)) AS distance
INTO #distances
FROM airports
WHERE runwaylength >= @minRunwayLength
AND country = @country
DECLARE @sql VARCHAR(200)
SET @sql = "SELECT"
IF @maxToReturn <> -1 BEGIN
SET @sql = @sql + " TOP " + CAST(@maxToReturn as varchar(30))
END
SET @sql = @sql + " * FROM #distances LEFT JOIN airports on #distances.id = airports.id"
IF @maxDistance <> -1 BEGIN
SET @sql = @sql + " WHERE distance <= " + CAST(@maxDistance as varchar(30))
END
SET @sql = @sql + " ORDER BY distance"
exec(@sql)
GO
So basically, without going into my whole schema, I'm trying to pass a variable amount of data to this SP. I could want the procedure to only select the top x records, or I could want it to select all of them. I might want it to have the distance in a where clause, and I might not.
I don't want to have to copy and paste my SQL four times (one set of code each for select top and where, select top without where, select where without top, and select without where or top); that's pretty bad programming practice, too.
So that's the problem. How do I fix it?
Thanks in advance for whatever help you can give,
-Starwiz
July 7, 2004 at 11:09 am
Instead of using Top n try setting the rowcount as follows.
SET ROWCOUNT @NumRows
SELECT ....
SET ROWCOUNT 0 -- Don't forget to do this as soon as you don't want to limit the number of rows for the query! Also do it inside your error handler if you have it just to be safe.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 7, 2004 at 11:27 am
That sounds good, but what about the where clause that may or may not be there? Do I need to duplicate my code within an IF statement, or is there a better way?
July 7, 2004 at 12:31 pm
You could do something like
WHERE distance <= ISNULL(@MaxDistance, distance)
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 7, 2004 at 12:47 pm
Given the simplicity of the SQL I would simply duplicate the query on the other side of an IF.
I've got something similar in some of my procs, but I have some really convoluted series of parameters.
What I found was that trying to write a single SQL statement that encompassed all possibilities resulted in code that worked, but had a high cost.
Breaking out the different functionality meant that the majority of users whose parameters triggered one of the simple query implementations got a very fast result without being penalised by those who triggered the more expressive queries.
Sometimes the long way works better.
July 8, 2004 at 7:48 am
If its impractical to go back into your app and change the -1 @maxdistance parameters to NULL, for the IsNull() implementation above, you could also do:
WHERE distance <=
CASE @maxdistance
WHEN -1 THEN distance
ELSE @maxdistance
END
July 8, 2004 at 8:35 am
hm...okay. Last question for now:
If I do end up just duplicating the code, is there a better way to do the second query than this?
SELECT * FROM
(SELECT *,
dbo.DistanceBetween(@latitude, @longitude, airports.latitude, airports.longitude) AS distance
FROM airports
WHERE runwaylength >= @minRunwayLength
AND country = @country) DerivedTbl
WHERE distance <= @maxDistance
ORDER BY distance
That is, do I need to have a subquery in order to sort and filter by a calculated column? That's always seemed really silly to me, but I haven't been able to figure it out otherwise.
Thanks for all the help, everyone; I really appreciate it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply