如何在SQL中编写存储过程 [英] How to write a stored procedure in SQL

问题描述

我有一个包含以下字段的员工表：

EmpId，EmpName，DeptID，Salary，editby和editedon。



我想要创建一个存储过程，将EmpId作为输入参数，并将生成显示剩余字段的输出

我创建了以下存储过程：

I have an employee table with following fields :
EmpId , EmpName , DeptID , Salary , editedby and editedon .

I want to create a stored procedure that will take EmpId as input parameters and will produce output showing remaining fields
I have created the following stored procedure :

CREATE PROCEDURE  Employeedetails 
( 
	@EmpId INT,
	@EmpName NVARCHAR(50) OUT ,
	@DeptID INT OUT ,
	@Salary INT OUT 
)	
AS
BEGIN
	
	SET NOCOUNT ON;

    
	--SELECT  @EmpName= EmpName, 
	--        @DeptID = DeptID , 
	--        @Salary =salary FROM employee
    --WHERE  EmpId =@EmpId
    
END 

 Declare @EmpName as nvarchar(50)
 Declare @DeptID as int
 Declare @Salary as int 
 Execute Employeedetails 1 , @EmpName output , @DeptID output , @Salary output 
 select @EmpName ,@DeptID , @Salary 

我得到的输出显示只有1个条目来自桌子。我希望输出应该显示所有条目。请帮忙。

I got the output showing only 1 entry from the table . I want the output should show all the entries . Please help .

推荐答案

CREATE PROCEDURE Employeedetails 
@EmpId INT
AS
SELECT EMPID,EmpName,DeptID,salary FROM employee WHERE EmpId =@EmpId 

并执行以下声明

and Execute below statement

Employeedetails 1251

CREATE PROCEDURE Employeedetails
@EmpId INT
AS
begin
SELECT EMPID,EmpName,DeptID,salary FROM employee WHERE EmpId =@EmpId
end

and than execute

exec Employeedetails 12

you will get reaming columns

这篇关于如何在SQL中编写存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！