Redirection - 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

SQL Server Development (2000)

Redirection

Author

Topic

salpesh
Starting Member

5 Posts

Posted - 2002-08-22 : 14:55:45

Hi;

I have a store procedure in my SQL 7.0. What I am trying to do is, I have a form where someone can select "State" and Insert the "City" name and search for an Agent. The query I have bring the result back from database if hose matches. But I would like to do is, If the "city" is not in the database, then show all the Agents in that State. Is this possible using Store Procedures? Can someone help me.

Thanks
Alpesh

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-22 : 15:14:29

Certainly. Here's a couple of ways . Whilst the top one may look more compact it will not to be as efficent as the longer v2 because it cannot effectively use an index on city. It depends on the size of the tables involved and your data distribution but I would recommend the second option. As an example - using the pubs database with the titles table (18 rows) then the cost difference between the two procedures is a mere 0.04% with indexes on title and type (representing state and city in your query) - move up to 500 rows and the cost difference grows to 33 %.


CREATE PROC v1 @state char(2),@city varchar(20)=NULL
AS
SET NOCOUNT ON

SELECT Agents FROM Table
WHERE state=@state 
AND(@city IS NULL OR city = @city)

RETURN
GO

CREATE PROC v2 @state char(2),@city varchar(20)=NULL
AS
SET NOCOUNT ON

IF @city IS NULL
BEGIN
  SELECT Agents FROM Table
  WHERE state=@state 
END
ELSE
BEGIN
  SELECT Agents FROM Table
  WHERE state=@state 
  AND city=@city
END

RETURN
GO

HTH
Jasper Smith

Edited by - jasper_smith on 08/22/2002 15:38:07

salpesh
Starting Member

5 Posts

Posted - 2002-08-22 : 15:43:53

Thanks for your reply. But I made it work the other way. Here is how I did it.

CREATE PROCEDURE dbo.sp_CityState
(
@City varchar(50), @State varchar(50)
)
AS
BEGIN
if @city = '*'
Begin
set @city = '%'
End else
Begin
set @city = @city + '%'
End

If (SELECT COUNT (*)
FROM gtlic.Customers
WHERE City LIKE @City AND State = @State ) = 0

Begin
SELECT *
FROM gtlic.Customers
WHERE State = @State
End Else

Begin

SELECT *
FROM gtlic.Customers
WHERE City LIKE @City AND State = @State

End

RETURN(0)

END

-Alpesh

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-22 : 16:05:02

However my v2 procedure above is at least twice as efficent

On another note, don't call your stored procedure's sp_xxxx.
You will incur a COMPILE lock every time you call the procedure regardless if you owner qualify it due to SQL's rules for scanning the procedure cache assuming the procedure is in the master database due to the sp_prefix. COMPILE locks can cause serialisation of access to procedure compile plans and can cause blocking (although rarely noticeable) - however they are easily avoided and a waste of resource so its best practice to not call user stored procedures sp_xxx and to always owner qualify them whan calling them from code.

HTH
Jasper Smith

Edited by - jasper_smith on 08/22/2002 16:10:42

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-08-22 : 16:14:04

Jasper,
I usually name my stored procs p_ProcName
Will that cause COMPILE locks? Or does SQL Server look for "sp_"?

Thanks!
Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-22 : 16:32:49

sp_xxxx is the special case (as well as not specifying the owner) - you should be safe

HTH
Jasper Smith

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-22 : 16:44:31

Jasper,

Doesn't your answer only deal with no value of @City being passed in, rather than (as I understood the question to be) the value of @City that is passed, returns 0 records?

Salpesh, you may find using IF exists... a faster method than IF SELECT Count(*)... = 0. Of course these are inverse statements, so you'll need to flip-flop your ELSE with your THEN.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-22 : 17:10:23

Quite right.
That'll teach me to read questions properly

HTH
Jasper Smith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources