如何使用 SQL Server 中的存储过程更新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21995568/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update a table using stored procedures in SQL Server
提问by user3096627
I have created a table in SQL Server called "Employee", and now I want to update the table using a stored procedure.
我在 SQL Server 中创建了一个名为“Employee”的表，现在我想使用存储过程更新该表。
The table has emp_name, emp_codeand statuscolumns. Assume the table has three records: initially, in the stored procedure I want to fetch the last two records using a select statement and I have to the fetched records' statusto 'Y'.
该表有emp_name,emp_code和status列。假设该表有三条记录：最初，在存储过程中，我想使用 select 语句获取最后两条记录，我必须将获取的记录'status改为'Y'。
I have written a stored procedure, but it's not affecting the original table. Kindly suggest the query for the stored procedure.
我写了一个存储过程，但它不影响原始表。请建议存储过程的查询。
Here is the query I have made:
这是我所做的查询：
create procedure updtdemployee As
select e.Emp_name ,e.Circle
from employee e
where e.Emp_Code ='2501' or e.Emp_Code='2502'
begin
update employee set status='Y' where Emp_name = employee.Emp_name
end
回答by Maryam Arshi
You don't need the Select part,just do the update.
您不需要 Select 部分，只需进行更新。
CREATE PROCEDURE updtdemployee
@employeeID INT
AS
BEGIN
UPDATE employee
SET status='Y'
WHERE Emp_Code = @employeeID
END
If you want to do it Static you can use this
如果你想做静态，你可以使用这个
CREATE PROCEDURE updtdemployee
AS
BEGIN
UPDATE employee
SET status='Y'
WHERE Emp_Code = 2501 or Emp_Code = 2502
END
回答by Talspaugh27
according to your question "I wants to fetch the last two records" if this is ONLY supposed to ever affect the last 2 records,
根据您的问题“我想获取最后两条记录”，如果这只会影响最后两条记录，
CREATE PROCEDURE updtdemployee
AS
BEGIN
UPDATE employee
SET status='Y'
WHERE Emp_Code in (select top 2 Emp_Code from employee order by Emp_Code desc)
END
I am purely guessing on the way you want to order this but is this what you were looking for?
我纯粹是在猜测您要订购的方式，但这就是您要找的吗？
回答by Bha15
*Try below code
*试试下面的代码
Create Procedure UpdateRecord (@emp_code int)
as
begin
update employee set status= 'Y'
where emp_code=@emp_code
end
*execute as below
*执行如下
exec UpdateRecode 3
3 is your emp_code. please change as your requirement.
3 是你的 emp_code。请根据您的要求进行更改。
回答by Bha15
-- =============================================
-- Author: XYZ
-- Create date: xx-xx-xxxx
-- Description: Procedure for Updating Emp Detail
-- =============================================
CREATE PROCEDURE [dbo].[SP_EmpLoyee_Update]
(
@EmpCode bigint=null,
@EmpName nvarchar(250)=null,
@MNumber nvarchar(250)=null,
@Status int=null,
@LoginUserId nvarchar(50)=null,
@Msg nvarchar(MAX)=null OUTPUT
)
AS
BEGIN TRY
UPDATE tbl_Employees
SET
EmpName=@EmpName,
MNumber=@MNumber,
Status=@Status,
ModificationDate=GETDATE()
WHERE EmpCode=@EmpCode
SET @Msg='Employee Updated Successfully.'
END TRY
BEGIN CATCH
SET @Msg=ERROR_MESSAGE()
END CATCH
GO
