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.
| Author |
Topic |
|
under2811
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-06-05 : 03:05:08
|
| Hi I am wana to know why we every time drop the procedure and again create it as below...will that impact on performance or compilation of SP???Use NorthwindGOIF OBJECT_ID('dbo.ListCustomersByCity') IS NOT NULL DROP PROC dbo.ListCustomersByCityGOCREATE PROCEDURE dbo.ListCustomersByCity @Country nvarchar(30)='%'ASSELECT City, COUNT(*) AS NumberOfCustomersFROM CustomersWHERE Country LIKE @CountryGROUP BY CityGOT.I.A |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-05 : 03:52:57
|
| You will impact performance in as far as everytime this is re-created and run again, the SP will need to create a new query plan.What do you mean about impacting compilation?!? |
 |
|
|
under2811
Constraint Violating Yak Guru
366 Posts |
Posted - 2008-06-05 : 03:56:16
|
| u mean it is bad practise to do it like this...T.I.A |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-06-05 : 04:02:13
|
| In 2005, yes, unless there is a very specific reason for it. If you are changing the SP constantly, then why are you using an SP, if the changes are significant, then its not a problem as it would need to get a new plan anyway. |
|
|
|
suresha_b
Yak Posting Veteran
82 Posts |
Posted - 2008-06-05 : 06:04:34
|
| ALTER can be used instead of DROP and CREATE.If you DROP and CREATE, you have to again GRANT previlages. |
|
|
|
|
|
|
Drop and again create SP will that help???