How to Write single proc for all operations?

I want to write Single stored proc for Insert,delete and update statements.Can you please help me?
more ▼

asked Jul 11, 2012 at 11:52 AM in Default

tiru5796 gravatar image

0 1 1 2

Can you please give us some information on the code you wish to include?
Jul 11, 2012 at 12:00 PM Mrs_Fatherjack
Without wishing to sound flippant - why on earth would you want to do this?
Jul 11, 2012 at 12:04 PM Kev Riley ♦♦
Jul 11, 2012 at 12:06 PM WilliamD

+1 to Kev...

...and are you looking to write one procedure to do all of these operations against a single table, or are you wanting one procedure to run against all tables? (The first is relatively straightforward; the second, less so.)
Jul 11, 2012 at 12:08 PM ThomasRushton ♦
@WilliamD - I hope you're right, but it didn't sound like it
Jul 11, 2012 at 12:10 PM Kev Riley ♦♦
show all comments (comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Have you looked at the MERGE command? That allows you to pass data through a single statement and then conditionally insert, update or delete as needed. Here's the MSDN entry: http://msdn.microsoft.com/en-us/library/bb510625.aspx
more ▼

answered Jul 11, 2012 at 01:33 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Thank you all for replies!!! I got the answer from my friend!!!
more ▼

answered Jul 12, 2012 at 06:20 AM

tiru5796 gravatar image

0 1 1 2

What was that answer? Edit your post and list it out so that others who are interested in the same thing that see your question will know what the answer was.
Jul 12, 2012 at 07:56 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
 @StudentID INT ,
 @SName CHAR(20) ,
 @SAddress CHAR(40) ,
 @PresentClassID INT ,
 @SubjectID INT ,
 @SMarks INT ,
 @Flag INT
 IF @Flag = 1 -- @Flag=1 Inserting the Compltely new record into the database tables

 VALUES  ( @StudentID, @SName, @SAddress )

 INSERT  INTO Student_Marks
 VALUES  ( @StudentID, @PresentClassID, @SubjectID, @SMarks )


 IF @Flag = 2 --- @Flag=2Inserting the already existing record of the student

 INSERT  INTO Student_Marks
 VALUES  ( @StudentID, @PresentClassID, @SubjectID, @SMarks )


 IF @Flag = 3 --- @Flag=3 Updating the Marks and Address of the Student

 UPDATE  Student
 SET     Address = @SAddress
 WHERE   Stud_Id = @StudentID

 UPDATE  Student_Marks
 SET     Marks = @SMarks
 WHERE   Subject_Id = @SubjectID
 AND Class_Id = @PresentClassID
 AND Stud_Id = @StudentID


In this way I will pass all the variables from my VB6 application with an extra reference to Flag . So If that condition satisfies then the action will perform otherwise don't. In this way We can write only one Stored proc for all the operations.

more ▼

answered Jul 12, 2012 at 09:05 AM

tiru5796 gravatar image

0 1 1 2

But if you've already worked out that the student exists (to set the right flag), why not call a different sp?
Jul 12, 2012 at 10:43 AM Kev Riley ♦♦
Yes, You can call other SPs also but it is the one way of writing less number of stored procs instead of different Sps for different operations.
Jul 13, 2012 at 02:38 AM tiru5796
Of course, everything in a single procedure like this means that all queries are compiled at the same time with a single set of parameter values. You might see poor performance for some queries since they'll be compiled with less than optimal values.
Jul 16, 2012 at 08:58 AM Grant Fritchey ♦♦
@Grant: No all the queries are not complied at the same time.One query executed at a time based on the condition satisified in the IF CALUSE.ok
Jul 16, 2012 at 09:56 AM tiru5796
But all queries within the procedure you outlined will be compiled the first time that procedure is called. That's how it absolutely works.
Jul 16, 2012 at 10:59 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 11, 2012 at 11:52 AM

Seen: 1370 times

Last Updated: Jul 16, 2012 at 11:35 PM