x

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 '12 at 11:52 AM in Default

tiru5796 gravatar image

tiru5796
0 1 1 2

Can you please give us some information on the code you wish to include?
Jul 11 '12 at 12:00 PM Mrs_Fatherjack
Without wishing to sound flippant - why on earth would you want to do this?
Jul 11 '12 at 12:04 PM Kev Riley ♦♦
Jul 11 '12 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 '12 at 12:08 PM ThomasRushton ♦
@WilliamD - I hope you're right, but it didn't sound like it
Jul 11 '12 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 '12 at 01:33 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
91k 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 '12 at 06:20 AM

tiru5796 gravatar image

tiru5796
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 '12 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
 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.

more ▼

answered Jul 12 '12 at 09:05 AM

tiru5796 gravatar image

tiru5796
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 '12 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 '12 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 '12 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 '12 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 '12 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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x128
x103
x74
x51

asked: Jul 11 '12 at 11:52 AM

Seen: 1060 times

Last Updated: Jul 16 '12 at 11:35 PM