Create a stored procedure to display last names-CodePudding

I am trying to create a procedure to display the last name as "Name" from the employee table for a specific department. The requirement is as below:

The name of the procedure     : EmployeesDept
Name of the argument as input : DeptNo

I am trying to use the following syntax:

CREATE PROCEDURE Employeesdept(@Deptno char)
AS
    DECLARE 
BEGIN
    SELECT lastname AS name
    FROM employee
    WHERE workdept = @Deptno
END
GO

EXECUTE Employeesdept @Deptno
GO

But I don't know why the procedure is not working.

CodePudding user response：

Much better then procedure is Table Valued Function (TVF).

CREATE FUNCTION Employeesdept 
(  
    @Deptno varchar(30)
)
RETURNS TABLE 
AS
    RETURN 
        SELECT lastname as name
        from employee
        where workdept=@Deptno

Big advantage is that TVF return Table, therefore you can use it in other queries:

SELECT * FROM Employeesdept(@Deptno='your_dept_no')

You don't want to use procedures for data selection, but for data transformation.

CodePudding user response：

CREATE  PROCEDURE Employeesdept(@Deptno char(3))
as
Begin
SELECT lastname as Name
from employee
where workdept=@Deptno
end
go

CodePudding user response：

CREATE  PROCEDURE Employeesdept(@Deptno CHAR(100))
AS
Begin
SELECT lastname as name
from employee
where workdept=@Deptno
end
go

EXECUTE YOUR PROCEDURE

exec [dbo].[Employeesdept] @Deptno=VALUE