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

avatar 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
  • 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

avatar image

Grant Fritchey ♦♦
137k 20 43 81

(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

avatar 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
 CREATE PROCEDURE spForAllOperations
  @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
  INSERT  INTO Student
  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

avatar 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.

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: 1576 times

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

Copyright 2016 Redgate Software. Privacy Policy