creating 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

and I am trying using 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：

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

EXECUTE YOUR PROCEDURE

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

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