can i perform different operations in one stored procedure

Question

0.00/5 (No votes)

See more:

i have created one stored procedure in which i performed insert query below is the code

ALTER PROCEDURE sp1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
(

@name nvarchar(50),
@age nvarchar(50)
)

AS
/* SET NOCOUNT ON */
insert into customer values(@name,@age)
RETURN

now what i want is that i could perform update and delete and select query in the same stored procedure.....................and in front end when i click on select, insert, update , delete button its respective operation should work taking from one stored procedure.....

how can i achieve this..............
regards,

Posted 10-Jan-13 1:34am

shivani 2013

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Zafar Sultan · Answer 1 · 2013-01-10T01:47:00

SQL

CREATE PROCEDURE ASampleProcedure
(
@id int,
@name varchar(50),
@age int,
@option int
)
AS
BEGIN
if @option = 1
BEGIN
--insert record
insert into customer values (@name, @age)
select 'Record inserted.' as Result
END
else
BEGIN
if @option = 2
BEGIN
--update record
update customer set [Name] = @name, [Age] = @age where ID = @id
select 'Record updated.' as Result
END
else
BEGIN
--deleterecord
delete from customer where ID = @id
select 'Record deleted.' as Result
END
END
END

From your front end pass parameters and option (1,2 or 3) This single procedure will perform all the operations.

pranathis012 · Answer 2 · 2013-01-10T01:58:00

yes, you can do it you can pass one more parameter called @Trans_Type varchar type
and the call that parameter in page itself.

ex

SQL

IF @Trans_Type='select'
--select query
else if @trans_type='delete'
--delete query
else if @trans_type='insert'
--insert query

like that you can use it no problem in that

Tejal S · Answer 3 · 2013-01-10T01:42:00

Yes you can very well do that using CASE.

For more info refer the below link

http://briannoyes.net/2005/09/29/GeneratingAGoodStoredProcedureCRUDLayerWithCodeSmith.aspx[^]

http://www.databasejournal.com/features/mssql/article.php/3082201/Implementing-CRUD-Operations-Using-Stored-Procedures-Part-1.htm[^]

can i perform different operations in one stored procedure

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0