|
|
Posted by serge on 10/14/05 05:30
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.EMPLOYEEBYID
@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 = 'EMPLOYEEBYFIRSTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAME
@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 = 'EMPLOYEEBYLASTNAME' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAME
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAME
@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 EMPLOYEEBYFIRSTNAME 'PQR'
GO
EXEC EMPLOYEEBYLASTNAME 'DEF1'
GO
-- Now if I use a View instead?
if exists (select * from sysobjects where name = 'EMPLOYEESEARCHVIEW' and
xtype = 'V')
drop view EMPLOYEESEARCHVIEW
GO
CREATE VIEW EMPLOYEESEARCHVIEW
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 = 'EMPLOYEEBYIDNEW' and xtype
= 'P')
drop procedure EMPLOYEEBYIDNEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYIDNEW
@ID INT
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE ID = @ID
GO
if exists (select * from sysobjects where name = 'EMPLOYEEBYFIRSTNAMENEW'
and xtype = 'P')
drop procedure EMPLOYEEBYFIRSTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYFIRSTNAMENEW
@FIRSTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE FIRSTNAME = @FIRSTNAME
GO
if exists (select * from sysobjects where name = 'EMPLOYEEBYLASTNAMENEW' and
xtype = 'P')
drop procedure EMPLOYEEBYLASTNAMENEW
GO
CREATE PROCEDURE dbo.EMPLOYEEBYLASTNAMENEW
@LASTNAME VARCHAR(25)
AS
SELECT *
FROM EMPLOYEESEARCHVIEW
WHERE LASTNAME = @LASTNAME
GO
-- Sample calls to these stored procedures.
EXEC EMPLOYEEBYIDNEW 5
GO
EXEC EMPLOYEEBYFIRSTNAMENEW 'PQR'
GO
EXEC EMPLOYEEBYLASTNAMENEW 'DEF1'
GO
Navigation:
[Reply to this message]
|