我可以在一个存储过程中执行不同的操作 [英] can i perform different operations in one stored procedure

问题描述

我创建了一个存储过程，其中我执行下面的插入查询是代码



更改程序sp1

/ *

（

@ parameter1 int = 5，

@ parameter2 datatype OUTPUT

）

* /

（



@name nvarchar（50），

@age nvarchar（50）

）





AS

/ * SET NOCOUNT ON * /

插入客户价值（@ name，@ age）

返回









现在我想要的是我可以在同一存储过程中执行更新和删除并选择查询............... ......在前端，当我点击选择，插入，更新，删除按钮时，其各自的操作应该从一个存储过程中工作.....





我怎样才能实现这个..............

问候，

解决方案

  CREATE   PROCEDURE  ASampleProcedure 
（
  @ id   int ，
  @ name   varchar （ 50 ），
  @ age   int ，
  @ option   int  
）
  AS  
  BEGIN  
  if   @ option  =  1  
  BEGIN  
   -   插入记录 
 插入 进入客户值（< span class =co de-sdkkeyword> @ name ， @ age ）
 选择 ' 记录已插入。'  as 结果
  END  
  else  
  BEGIN  
 如果  @ option  =  2  
  BEGIN  
   -   更新记录 
 更新客户设置 [名称] = < span class =code-sdkkeyword> @ name ，[Age] =  @ age  其中 ID =  @ id  
 选择 '< /跨度> 记录更新。'  as 结果
  END  
  else  
  BEGIN  
   -    deleterecord  
  delete  来自客户其中​​ ID =  @ id  
 选择 ' 记录已删除。'  as 结果
  END  
  END  
  END  

来自您的前端传递参数和选项（ 1,2或3）这个程序将执行所有操作。

是的，你可以做到你可以通过o更多参数叫@Trans_Type varchar类型

和页面本身的参数调用。



ex

  IF  @ Trans_Type = ' < span class =code-string> select' 
   -    select query  
  else   if  @ trans_type = '  delete' 
   -    delete查询 
  else   if  @ trans_type = '  insert' 
   -   插入查询 

就像你可以使用它没有问题

是你可以使用CASE来做到这一点。



欲了解更多信息，请参阅以下链接



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 [ ^ ]

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,

解决方案

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.

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

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

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[^]

这篇关于我可以在一个存储过程中执行不同的操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！