March 3, 2011 at 4:26 am
Hai...
if i execute a procedure as my code starts as follows...
PROCEDURE PROC__INS_FUNCTION
GO
IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('PROC_INS_FUNCTION')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
DROP PROCEDURE PROC_INS_FUNCTION
GO
CREATE PROCEDURE PROC_INS_FUNCTION
()
...
...
...
after executing(creating) above procedure.. ... will it clear existing cache?....will it create new execution plan when next time this sp called?... any help on this?
How can i make sure a stored procedure is compiled or not?. when it is compiled?
Thanks in advance...
March 3, 2011 at 5:03 am
well, your proc's constructions not going to work, but i realize that was a prototype.
you cannot have a GO command in the body of a procedure; to do what you want, you;d have to switch to dynamic sql to drop and create.
aside from that, yes, the first tjme the proc is called after that, a new execution plan will be created; the old plan would no longer exist. you'd also lose any permissions assignments on the proc, so you'd want to script those out too.
can i ask why you are doing this:? the reason i ask is i suspect you are seeing a procedure that is slowing down over time, and your solution has been to drop and recreate it.
the slowing down is usually attributable to out of date statistics...if you update the statistics on the tables the proc was using more often, you wouldn't have the issue, i suspect.
Lowell
March 4, 2011 at 11:57 pm
Hi Lowel,
We are making all business logic of the project into SP format.. so all logic is written as a SP.. having following skelton...
IF EXISTS (SELECT * FROM sysobjects
WHERE id = OBJECT_ID('PROC_PERSON_CREATION')
AND OBJECTPROPERTY(id, 'IsProcedure') = 1)
DROP PROCEDURE PROC_PERSON_CREATION
GO
CREATE PROCEDURE PROC_PERSON_CREATION
(
# parameters will be here
)
AS
SET NOCOUNT ON
DECLARE # local variable declaration here
BEGIN
#validation will be here
BEGIN TRY
BEGIN TRANSACTION
# every transactions will be written here
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT>0
ROLLBACK
#exception logic
END CATCH
END
---------------------------------------------->Procedure Ends here<----
1. How good is my SP skelton?
2. How can i make sure wether the procedure is executed or not?
3. how can i make sure procedure is recompiled to the latest modification?
Kindly help me on this....
Thanks in Advance... thanks for ur valuable ideas.
March 7, 2011 at 5:23 am
BeginnerBug (3/4/2011)
1. How good is my SP skelton?
2. How can i make sure wether the procedure is executed or not?
3. how can i make sure procedure is recompiled to the latest modification?
1. I believe you are referring to the format. Looks good. How about adding some comment about what the SP does?
2. Do you mean - how to make sure if the create procedure script is executed? You can check the messages window after executing the script to do this.
3. Recompiled to the latest modification? :unsure:
- arjun
https://sqlroadie.com/
March 7, 2011 at 6:05 am
When you alter or drop a stored proc, any cached execution plan is immediately invalidated, you don't have to do anything to cause that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply