如何使用SP插入和删除行 [英] How to use insertion and deletion of row using an SP

问题描述

我有2个存储过程，用于插入详细信息和更新详细信息，如下所示。我需要将这两个程序结合在一起，因为在我的项目中我有一个提交按钮，它用于插入和更新操作。更新意味着编辑已存在的行并使用新值更新它。

Hi,
I have 2 stored procedures for insert details and update details as shown below. I need to combine these 2 procedures together because in my project I have a submit button, it is used for both insertion and update operation. Update means that edit already existing row and update it with new values.

CREATE PROCEDURE spInsertaItem @ItemCode VARCHAR(50)
    ,@ItemDescription VARCHAR(250)
    ,@ItemCategoryID INT
AS
BEGIN
    BEGIN TRANSACTION

    DECLARE @ItemID INT

    SELECT @ItemID = coalesce((
                SELECT max(ItemID) + 1
                FROM aItem
                ), 1)

    SELECT @ItemID = max(ItemID) + 1
    FROM Item

    SELECT @ItemCode = coalesce((
                SELECT max(ItemID) + 1
                FROM aItem
                ), 1)

    INSERT INTO aItem
    (
        ItemID
        ,ItemCode
        ,ItemDescription
        ,ItemCategoryID
    )
    VALUES
    (
        @ItemID
        ,@ItemCode
        ,@ItemDescription
        ,@ItemCategoryID
     )
    
    SELECT @TransactionId = @ItemID
    SELECT @TransactionNo = @ItemDescription

    COMMIT
END

我的更新SP

Its my update SP

CREATE PROCEDURE spUpdateItemHeader (
    @ItemID INT
    ,@ItemCode VARCHAR(50)
    ,@ItemDescription VARCHAR(250)
    ,@ItemCategoryID INT
AS
BEGIN
    SELECT 
         @ItemCode = ItemCode
        ,@ItemDescription = ItemDescription
        ,@ItemCategoryID = ItemCategoryID
    FROM aItem
    WHERE ItemID = @ItemID
    AND @ItemDescription = ItemDescription

	BEGIN
		UPDATE Item
		SET ItemDescription = @ItemDescription
			,ItemCategoryID = @ItemCategoryID
                WHERE ItemID = @ItemID
        END

推荐答案

尝试运行存储过程。

你必须声明变量 @operation 带 int 数据类型如下图所示，

现在你可以执行特定的查询了只需从后端传递 @operation 。



您可以将两个查询放在一个操作中希望。

您可以使用此查询执行多项操作.... :)

try operational stored procedure.
You have to declare variable @operation with int datatype like as shown below,
Now you can execute perticular query with just passing @operation from backend.

You can put both query in one operation as you wish.
You can execute multiple operations with this query.... :)

CREATE PROCEDURE SP_MyQuery
     @ItemCode VARCHAR(50)=''
    ,@ItemDescription VARCHAR(250)=''
    ,@ItemCategoryID INT=0
	,@ItemID INT=0
	,@operation int=0
	AS
BEGIN
	IF(@operation=1)
		begin
			--What ever you want to execute, insert query , update query , delete query, select query
		end
	ELSE IF(@operation-2)
		BEGIN
			--What ever you want to execute, insert query , update query , delete query, select query
		END
	
END

如果您使用的是MSSQL 2008或更高版本，请查看 MERGE 声明。



如果记录不存在，则执行相当于insert语句的操作，如果记录不存在则执行更新语句。



希望它有所帮助。

If your using MSSQL 2008 or above, check out the MERGE statement.

This will perform the equivalent of an insert statement if the record did not already exist, and an update statement if it did.

Hope it helps.

这篇关于如何使用SP插入和删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！