I am hoping someone can help me out with updating stored procedures in SQL Server 2000! I have stored procedures that point to other stored procedures based on a variable. An example is:
<b>
CREATE PROCEDURE spvarChairMsgSideNews
(@PracID int)
as
if @PracID = 1
execute spGastroenterology_ChairMessage
else
if @PracID = 2
execute spCardiology_ChairMessage
else
print ''
GO
</b>

What I need is a way to add a new "IF" statement to all the neccessary stored procedures when a new practice is added to the database (I work for a college of medicine so PracID is a Practice ID). So, If I added Dermatology to the "Practices" table, the stored procedure above would be updated to:
<b>
CREATE PROCEDURE spvarChairMsgSideNews
(@PracID int)
as
if @PracID = 1
execute spGastroenterology_ChairMessage
else
if @PracID = 2
execute spCardiology_ChairMessage
else
if @PracID = 3
execute spDermatology_ChairMessage
else

print ''
GO
</b>

It could either take place when a new practice was added to the "practices" table or when I add the new procedure: spDermatology_ChairMessage. The new addition of code would need to take place on more than one SP.

Any help would REALLY be appreciated! Also, If you have any suggestions on how to do this a better way, you are more than welcome to voice them.

Thanks for any help in advance...

Rip<b></b>

What do the separate sp's spCardiology_ChairMessage etc. do?

I can't help thinking that there's something amiss with this method and that there should be a more elegant solution.


Regards

Owen.

<hr></hr>
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/Shop.htm

Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

If you can give me a more elegant way, please feel free to do so. The seperate SP's return the Chair's message, the Chair's photo and a featured article for a practice's home page. There are others that pull info for the navigation on the left-hand side. You can see an example of Cardiology's site at www.topdocsonline.com/Cardiology/

Here is the code again from my original post so you don't have to scroll up and down to see both:

<b>CREATE PROCEDURE spvarChairMsgSideNews
(@PracID int)
as
if @PracID = 1
execute spGastroenterology_ChairMessage
else
if @PracID = 2
execute spCardiology_ChairMessage
else
print ''
GO
</b>

The code for spCardiology_ChairMessage is:


<b>CREATE PROCEDURE dbo.spChairMsgSideNews_Cardiology
AS
SELECT *
FROM ChairMsgSideNews
WHERE PracticeID = 14

GO
</b>

Because about 95% of our site is database driven, I spent a lot of time reading up on optimizing SQL databases. One of the articles on DMXZone.com linked to a website that contained many articles on SQL optimization. One of them said that having an "IF THEN" SP is the best way of improving speed of the stored procedures because each SP will remain in the cache rather than the cached version getting changed each time a new @PracID is passed.

Thanks for taking the time to respond to my post. I really appreciate the help!

Rip

Forgive me if I'm wrong, but it appears that you have more then one identity for each practice (confused).

For example you have PracID 1 for Cardiology, then you have PracticeID 14 for Cardiology in the ChairMessageNews table.

Why wouldn't you have one unique ID for the practice of Cardiology which is then used throughout the database:

Something like:

tblPractices:

PracID     Practice
----------------------------------
1             Neurology
2             Cardeology
3             Epidemiology
etc.....

tblChairMessageNews:

PracID     Message
----------------------------------
1             Blah, Blah, Blah
2             Rhubarb, Rhubarb, Rhubarb
3             Foo, Foo, Foo
etc.....


Now you would only need a very simple, unchanging SP to retreive the relevant Chair's Message according to the Practice selected:

--------------------------------------------------
CREATE PROCEDURE dbo.spChairMsgSideNews_Cardiology

@PracID int

AS

SET NOCOUNT ON -- &lt;-- <font color=red>Another performance tip, prevents SQL Server from counting the number of rows affected by an INSERT, UPDATE, SELECT or DELETE command</font id=red>

SELECT * FROM ChairMsgSideNews WHERE PracID = @PracID

SET NOCOUNT OFF

Go
--------------------------------------------------


Regards

Owen.

&lt;hr&gt;&lt;/hr&gt;
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/Shop.htm

Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Edited by - oeastwick on 15 Jan 2003 18:20:09

Ah, sorry about the mixup. I made up the Practice IDs in spvarChairMsgSideNews for example purposes only. Because spVarChairMsgSideNews contained over 40 Practice ID I had to chop it down.
So it should read:

Example of variable controled SP:
<b>CREATE PROCEDURE spvarChairMsgSideNews
(@PracID int)
as
if @PracID = 1
execute spGastroenterology_ChairMsgSideNews
else
if @PracID = 2
execute spCardiology_ChairMsgSideNews
else
print ''
GO
</b>

and the code for spCardiology_ChairMessage is:

<b>CREATE PROCEDURE dbo.spCardiology_ChairMsgSideNews
AS
SELECT *
FROM ChairMsgSideNews
WHERE PracticeID = 2

GO

</b>

According to the article, if there was one SP like the one you suggested the cache of the SP would change each time a new variable was passed. By creating seperate non-variable SPs for each practice, each SP will always be in cache. It would be hard at this point to go back to one unchanging SP. It took me several weeks to update all my pages. Do you think there is hope for updating the SPs that accept the variables?

Thanks for you tip about "SET NOCOUNT ON". As soon as I submit this I will go back and add it to all my SPs.

Thanks again,

Rip

Have I got this right?

You want to create a stored procedure that will modify another stored procedure and create a new one when a new practice is added to the Practices table.

I'm pretty sure this isn't possible, but I'm not going to swear on it. I think you may have saved a few nanoseconds with the non-vatiable SP's only to create a nightmare DB admin job. The method you have used might well be useful for unchanging tasks, but not suitable for more dynamic portions of the database.

I'd like to take a look at the article you are refering to, can you post a link.

Regards

Owen.

---------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/Shop.htm

Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Hey, sorry it has taken so long to respond to your last reply. I was tied up with a web application which needed to be finished by this morning. Here is a link to the article: www.sql-server-performance.com/stored_procedures.asp I got to that site from another SQL site that DMXZone pointed to. The info explaining what I did begins at the heading "Here's another way to handle the problem of not knowing what parameters your stored procedure might face." Initially, I didn't see the need to add more practices since they rarely ever change. But, we now need to set up Centers for these practices as separate practices. For example, the "Center for Genetics Fetal Maternal Medicine" wants to be treated as a separate practice even though it is really just a subdivision of OBGYN which already has a site. If only I could go back in time!

It's an interesting article, however I don't think the method you have employed is particularly useful for what you are doing, the SELECT statement in the second SP's are very basic.

I think this extract from the article applies to your scenario:

<i>Of course re-compiling a plan for a simple 'SELECT *' from a single table will not give you a noticeable delay either (in fact, the overhead of an extra stored procedure call may be bigger then the re-compilation of "SELECT * FROM AnyTable", but as soon as the queries get bigger, this method certainly pays off.

The only downside to this method is that now you have to manage three stored procedures instead of one.</i>

Regards

Owen.

---------------------------------------------------
Used programming books and web development software for sale (UK only):
www.tdsf.co.uk/Shop.htm

Multiple Parameter UD4 / Access 2000 Search Tutorial:
www.tdsf.co.uk/tdsfdemo/

Edited by - oeastwick on 20 Jan 2003 21:45:14

I agree... I went with this option under the impression that I woudn't be adding any more practices... So, I guess I'll have to live with the current format for now. The only solution I have come up with is to create .SQL files of my procedures and change "create proc" to "alter proc".

Once again, thanks for taking the time out to lend me a hand. I am the only developer at my job, so I really do appreciate it when people like yourself volunteer your time to help me out when I have no one to turn to. I try to wait until I have exhausted all my resourses before posting messages online.

Thanks again Owen,

Rip