asked
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?
@Kev Riley - [MERGE command?][1] [1]:
http://technet.microsoft.com/en-us/library/bb510625.aspx
Can you please give us some information on the code you wish to include?
Without wishing to sound flippant - why on earth would you want to do this?
+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.)
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
Thank you all for replies!!! I got the answer from my friend!!!
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.
CREATE PROCEDURE spForAllOperations @StudentID INT , @SName CHAR(20) , @SAddress CHAR(40) , @PresentClassID INT , @SubjectID INT , @SMarks INT , @Flag INT AS IF @Flag = 1 -- @Flag=1 Inserting the Compltely new record into the database tables BEGIN INSERT INTO Student VALUES ( @StudentID, @SName, @SAddress ) INSERT INTO Student_Marks VALUES ( @StudentID, @PresentClassID, @SubjectID, @SMarks ) END ELSE IF @Flag = 2 --- @Flag=2Inserting the already existing record of the student BEGIN INSERT INTO Student_Marks VALUES ( @StudentID, @PresentClassID, @SubjectID, @SMarks ) END ELSE IF @Flag = 3 --- @Flag=3 Updating the Marks and Address of the Student BEGIN 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 END 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.
But if you've already worked out that the student exists (to set the right flag), why not call a different sp?
But all queries within the procedure you outlined will be compiled the first time that procedure is called. That's how it absolutely works.
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.
12 People are following this question.
