'Create a stored procedure to display last names [closed]

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.

sqlsql-serverprocedure


Solution 1:[1]

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('your_dept_no')

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

Solution 2:[2]

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

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Charlieface
Solution 2 Sadra M.
Solution 3

Related Questions