Before you start, please apply changes suggested in solution1 by
PIEBALDconsult[
^].
You can achieve that by extracting emptype to new table:
CREATE TABLE EmpTypes
(
EmpTypeID INT IDENTITY(1,1)
TypeDescription NVARCHAR(50)
)
INSERT INTO EmpTypes(TypeDescription)
SELECT DISTINCT EmpType
FROM Emp
ALTER TABLE Emp ADD COLUMN EmpTypeID INT REFERENCES EmpTypes(EmpTypeID)
UPDATE t1 SET t1.EmpTypeID = t2.EmpTypeID
FROM Emp AS t1 INNER JOIN EmpTypes AS t2 ON t1.EmpType = t2.TypeDescription
ALTER TABLE Emp DROP COLUMN EmpType
Note: above query could contain errors, i couldn't test it.
Finally, create procedure:
CREATE PROCEDURE GetObviousData
@startdate DATETIME,
@enddate DATETIME,
@emptype INT
AS
BEGIN
SELECT t1.<Field_List2>, t2.<Field_List>
FROM Emp AS t1 INNER JOIN EmpTypes AS t2 ON t1.EmpTypeID = t2.EmpTypeID
WHERE t1.EmpTypeId = @emptype AND t1.HireDate BETWEEN @startdate AND @enddate
END
MSDN documentation:
ALTER TABLE[
^]
UPDATE[
^]
CREATE PROCEDURE[
^]
CREATE TABLE[
^]