选择1 StoredProcedures中的Insert Update [英] Select Insert Update in 1 StoredProcedures

问题描述

我能够从以下查询执行插入和更新，但无法选择记录，在此过程中在何处以及如何编写选择查询。

  alter   PROCEDURE  usp_sel_DailyReport 
  @ Id   int  =  null ，
  @ WithdrawalAmount   int ，
  @ Balance   int ，
  @ BankName   varchar （  30 ），
  @ WorkerLabour   varchar （ 50 ），
  @ PurchaseMatrialAmount   int ，
  @ PurchaseAgainst   varchar （ 50 ），
  @ Comments   varchar （max）
  as  开始 
 如果（ @ ID     null ）
 将 插入 tbldailyreport（WithdrawalAmount，Balance，BankName，WorkerLabour，PurchaseMatrialAmount，PurchaseAgainst，Comments，IsActive， Date ，CreatedBy，LastUpdatedBy）
 值（ @ WithdrawalAmount ， @ Balance ， @ BankName ， @ WorkerLabour ， @ PurchaseMatrialAmount ， @ PurchaseAgainst ， @ Comments ， 1 ，Getdate（），'  Admin'，'  Admin'）
  else  
  update  tbldailyreport  set  WithdrawalAmount = @ WithdrawalAmount，Balance = @ Balance，BankName = @ BankName，WorkerLabour = @ WorkerLabour，PurchaseMatrialAmount = @ PurchaseMatrialAmount，PurchaseAgainst = @ PurchaseAgainst，Comments = @ Comments， Date  = GetDate（）
 其中 ID = @ ID 
  end  

我在哪里写在数据绑定中使用的相同过程中选择查询

 选择 Id， WithdrawalAmount，Balance，BankName，WorkerLabour，PurchaseMatrialAmount，PurchaseAgainst，评论来自 tbldailyreport 其中 IsActive = 1 

解决方案

只需编写一个新SP并将其绑定到数据绑定中。



样本选择SP如下：

  / *   Getstudentname是存储过程的名称* /  
 
 创建  PROCEDURE  Getstudentname（
 
  @ studentid   INT    -   输入参数，学生的学生 
 
）
  AS  
  BEGIN  
  SELECT 名字+ ' ' +姓氏 FROM  tbl_Students  WHERE  studentid = @ studentid 
  END  

欲了解更多信息，请查看： Sql Server - 如何在Sql server中编写存储过程 [ ^ ]



更新



您可以使用类似下面的内容。但这样有利有弊。有关更多信息，请参阅这个检查下面提到的文章。

 CREATE PROCEDURE [dbo]。[spTABLENAME] 
 @Function nvarchar = null，
 @ID int = null，
 @MoreVariables int = null 
 
 AS 
 BEGIN 
 SET NOCOUNT ON; 
 
 IF @Function ='UPDATE'
 BEGIN 
 UPDATE UPDATESTUFF WHERE ID = @ID; 
 END 
 ELSE IF @Function ='INSERT'
 BEGIN 
 INSERT INTO TABLENAME（STUFF）
 END 
 ELSE IF @Function ='SELECT'
 BEGIN 
 SELECT * FROM TABLENAME WHERE ID = @ID 
 END 
 ELSE IF @Function ='DELETE'
 BEGIN 
 DELETE * FROM TABLENAME WHERE ID = @ID 
 END 

欲了解更多信息： SOF



我希望这会对你有所帮助。

你可以尝试下面这样的，因为你不想使用任何其他参数

  alter  程序 usp_sel_DailyReport 
  @ Id   int  =  null ，
  @ WithdrawalAmount   int ，
  @ Balance   int ，
  @ BankName   varchar （ 30 ），
  @ WorkerLabour   varchar （  50 ）=  Null ，
  @ PurchaseMatrialAmount   int ，
  @ PurchaseAgainst   varchar （ 50 ），
  @ Comments   varchar （max）
  as   begin  
   -    Addittion  
  IF   @ WorkerLabour  =  NULL   OR  ISNULL（ @ WorkerLabour 。' '）= ' ' 
  BEGIN  
 选择 Id，WithdrawalAmount，Balance，BankName，WorkerLabour，PurchaseMatrialAmount，PurchaseAgainst，Comments 来自 tbldailyreport 其中​​ IsActive = 1 
  END  
  ELSE  
  BEGIN  
   -   Addittion  
  if （ @ ID     null ）
  insert   into  tbldailyreport（WithdrawalAmount，Balance ，BankName，WorkerLabour，PurchaseMatrialAmount，PurchaseAgainst，Comments，IsActive， Date ，CreatedBy，LastUpdatedBy）
  values （ @ WithdrawalAmount ， @ Balance ， @ BankName ， @ WorkerLabour ， @ PurchaseMatrialAmount ， @ PurchaseAgainst ， @ Comments ， 1 ，Getdate（），'  Admin'，'  Admin '）
  else  
  update  tbldailyre port  set  WithdrawalAmount = @ WithdrawalAmount，Balance = @ Balance，BankName = @ BankName，WorkerLabour = @ WorkerLabour，PurchaseMatrialAmount = @ PurchaseMatrialAmount，PurchaseAgainst = @ PurchaseAgainst，Comments = @ Comments ，日期 = GetDate（）
 其中 ID = @ ID 
 结束 
  END    -    Addittion  

Hi,
I am able to perform insert and update from the below query but cant select the records, where and how to write the select query in this procedure.

alter PROCEDURE usp_sel_DailyReport
@Id int = null,  
@WithdrawalAmount int,  
@Balance int,  
@BankName varchar(30),
@WorkerLabour varchar(50),
@PurchaseMatrialAmount int,
@PurchaseAgainst varchar(50),
@Comments varchar(max)
as begin
if(@ID is null)  
insert into tbldailyreport (WithdrawalAmount,Balance,BankName,WorkerLabour,PurchaseMatrialAmount,PurchaseAgainst,Comments,IsActive,Date ,CreatedBy ,LastUpdatedBy)
values (@WithdrawalAmount,@Balance,@BankName,@WorkerLabour,@PurchaseMatrialAmount,@PurchaseAgainst,@Comments,1,Getdate(),'Admin','Admin')  
else
update tbldailyreport set WithdrawalAmount=@WithdrawalAmount,Balance=@Balance,BankName=@BankName,WorkerLabour=@WorkerLabour,PurchaseMatrialAmount=@PurchaseMatrialAmount,PurchaseAgainst=@PurchaseAgainst,Comments=@Comments,Date = GetDate()
where ID=@ID
end

Where do i write the select query in the same procedure which is used in data binding

select Id,WithdrawalAmount,Balance,BankName,WorkerLabour,PurchaseMatrialAmount,PurchaseAgainst,Comments from tbldailyreport where IsActive=1  

解决方案

Just write a new SP and bind that into your data binding.

Sample Select SP is as below :

/*  Getstudentname is the name of the stored procedure*/

Create  PROCEDURE Getstudentname(

@studentid INT                   --Input parameter ,  Studentid of the student

)
AS
BEGIN
SELECT Firstname+' '+Lastname FROM tbl_Students WHERE studentid=@studentid
END

For more info check this : Sql Server - How to write a Stored procedure in Sql server[^]

UPDATE

You can use something like below.But there're pros and cons in this way.For more info about this check below mentioned article.

CREATE PROCEDURE [dbo].[spTABLENAME]
@Function nvarchar = null,
@ID int = null,
@MoreVariables int = null

AS
BEGIN
    SET NOCOUNT ON;

IF @Function = 'UPDATE'
    BEGIN
        UPDATE UPDATESTUFF WHERE ID = @ID;
    END
ELSE IF @Function = 'INSERT'
    BEGIN
        INSERT INTO TABLENAME (STUFF)
    END
ELSE IF @Function = 'SELECT'
    BEGIN
        SELECT * FROM TABLENAME  WHERE ID= @ID
    END
ELSE IF @Function = 'DELETE'
    BEGIN
        DELETE * FROM TABLENAME WHERE ID = @ID  
    END

For more info: SOF

I hope this will help to you.

You can try below like this as you don't want to use any other Parameter

alter PROCEDURE usp_sel_DailyReport
@Id int = null,  
@WithdrawalAmount int,  
@Balance int,  
@BankName varchar(30),
@WorkerLabour varchar(50) = Null,
@PurchaseMatrialAmount int,
@PurchaseAgainst varchar(50),
@Comments varchar(max)
as begin
--Addittion
IF @WorkerLabour = NULL OR ISNULL(@WorkerLabour.'') = ''
BEGIN
	select Id,WithdrawalAmount,Balance,BankName,WorkerLabour,PurchaseMatrialAmount,PurchaseAgainst,Comments from tbldailyreport where IsActive=1 
END
ELSE
BEGIN
--Addittion
if(@ID is null)  
insert into tbldailyreport (WithdrawalAmount,Balance,BankName,WorkerLabour,PurchaseMatrialAmount,PurchaseAgainst,Comments,IsActive,Date ,CreatedBy ,LastUpdatedBy)
values (@WithdrawalAmount,@Balance,@BankName,@WorkerLabour,@PurchaseMatrialAmount,@PurchaseAgainst,@Comments,1,Getdate(),'Admin','Admin')  
else
update tbldailyreport set WithdrawalAmount=@WithdrawalAmount,Balance=@Balance,BankName=@BankName,WorkerLabour=@WorkerLabour,PurchaseMatrialAmount=@PurchaseMatrialAmount,PurchaseAgainst=@PurchaseAgainst,Comments=@Comments,Date = GetDate()
where ID=@ID
end
END--Addittion

这篇关于选择1 StoredProcedures中的Insert Update的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！