Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

[serge]

Using "SELECT * " is a bad practice even
when using a VIEW instead of a table?

I have some stored procedures that are
identical with the difference of one statement
in the WHERE clause. If I create a single View
and specify also in this View the WHERE clause
that is common in these stored procedures, I
will have the new stored procecures changed to
be like:

SELECT * FROM View1
WHERE ID = @ID

Is it a good idea to do this by moving the
common SELECT statement to a View? Will it be
less performant compared to before?
Will this approach cause new problems on
the long run?

I would appreciate your help.

Thank you very much

Please find below a sample code for this.


USE Northwind
GO

if exists (select * from sysobjects where name = 'EMPLOYEE' and xtype = 'U')
drop table EMPLOYEE
GO
CREATE TABLE EMPLOYEE
(
ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
FIRSTNAME VARCHAR(25) NOT NULL,
LASTNAME VARCHAR(25) NOT NULL,
AGE TINYINT NOT NULL,
NOTES VARCHAR(200) NOT NULL,
SPECIALID INT NOT NULL
)
GO
INSERT INTO EMPLOYEE
SELECT 'ABC', 'ABC1', 35, 'Abc', 1 UNION ALL
SELECT 'DEF', 'DEF1', 36, 'Def', 1 UNION ALL
SELECT 'GHI', 'GHI1', 37, 'Ghi', 1 UNION ALL
SELECT 'JKL', 'JKL1', 38, 'Jkl', 1 UNION ALL
SELECT 'MNO', 'MNO1', 39, 'Mno', 1 UNION ALL
SELECT 'PQR', 'PQR1', 40, 'Pqr', 1
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYID' and xtype =
'P')
drop procedure EMPLOYEEBYID
GO
CREATE PROCEDURE dbo.EMPLOYEEBYI D
@ID INT
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRS TNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYFIRST NAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYF IRSTNAME
@FIRSTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLAST NAME' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTN AME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYL ASTNAME
@LASTNAME VARCHAR(25)
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
AND LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYID 5
GO
EXEC EMPLOYEEBYFIRST NAME 'PQR'
GO
EXEC EMPLOYEEBYLASTN AME 'DEF1'
GO


-- Now if I use a View instead?
if exists (select * from sysobjects where name = 'EMPLOYEESEARCH VIEW' and
xtype = 'V')
drop view EMPLOYEESEARCHV IEW
GO
CREATE VIEW EMPLOYEESEARCHV IEW
AS
SELECT ID, FIRSTNAME, LASTNAME, AGE, NOTES, SPECIALID
FROM EMPLOYEE
WHERE SPECIALID = 1
GO

-- And I create new Stored Procedures
if exists (select * from sysobjects where name = 'EMPLOYEEBYIDNE W' and xtype
= 'P')
drop procedure EMPLOYEEBYIDNEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYI DNEW
@ID INT
AS
SELECT *
FROM EMPLOYEESEARCHV IEW
WHERE ID = @ID
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRS TNAMENEW'
and xtype = 'P')
drop procedure EMPLOYEEBYFIRST NAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYF IRSTNAMENEW
@FIRSTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHV IEW
WHERE FIRSTNAME = @FIRSTNAME
GO

if exists (select * from sysobjects where name = 'EMPLOYEEBYLAST NAMENEW' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTN AMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYL ASTNAMENEW
@LASTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHV IEW
WHERE LASTNAME = @LASTNAME
GO

-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYIDNEW 5
GO
EXEC EMPLOYEEBYFIRST NAMENEW 'PQR'
GO
EXEC EMPLOYEEBYLASTN AMENEW 'DEF1'
GO