March 7, 2011 at 5:20 am
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
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...
March 7, 2011 at 2:23 pm
1) it really depends on what you're looking for. Basically it seems OK. I'm not sure I'd have the outer BEGIN...END wrapper because it's not really doing anything for you, but other than that, it looks OK. Have you compiled it? Does it do what you want?
2) When a stored procedure is called, unless you modify the behavior it returns a zero on successful completion. Check for that value in the calling code.
3) If you drop and recreate a procedure, the next time it gets called, it will recompile. Is that what you mean?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 8, 2011 at 12:37 am
Grant Fritchey (3/7/2011)
1) it really depends on what you're looking for. Basically it seems OK. I'm not sure I'd have the outer BEGIN...END wrapper because it's not really doing anything for you, but other than that, it looks OK. Have you compiled it? Does it do what you want?2) When a stored procedure is called, unless you modify the behavior it returns a zero on successful completion. Check for that value in the calling code.
3) If you drop and recreate a procedure, the next time it gets called, it will recompile. Is that what you mean?
First of all Thanks a lot Fritchey......
1. Yes.. I compiled it.Its works well
3. Yes.. i drop and recreate a procedure. how can i make sure it is recompiled?.. i mean previous plan cache were flushed out?..
As you suggested, i remove that BEGIN-End wrapper....
Thanks once again...
March 8, 2011 at 5:12 am
3. Yes, dropping a procedure also removes the plan from cache (well, marks it, it'll get removed later), so when you rebuild the proc, you get a new plan.
I just don't see the BEGIN...END wrapper doing anything. If it does nothing, I'd get rid of it. But it's not going to hurt things either.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 9, 2011 at 1:22 am
Thanks fritchey.....
March 9, 2011 at 6:52 am
BeginnerBug (3/9/2011)
Thanks fritchey.....
It's Grant, and you're welcome.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply