Repetitive SQL: What does it mean?

Question

Hi

In a different post I got a reply that tells about Repetitive SQL. Could you please explain what is Repetitive SQL?

http://stackoverflow.com/questions/2657459/sql-code-smells

I thought to make it a new post as it is a different subject. The reply says that use of "multiple stored procedures that perform the exact same joins but different filters" can be avoided using VIEWs.

Could you please give an example that can only be achieved using repetitive queries, if we are using Stored Proecure? [The same can be achieved without repetition when used VIEWS]

Thanks

Lijo

Answer 1

I think what the OP meant in this case is when you have stored procedures to return lets say User information

Something like

CREATE PROCEDURE UserByID (
        @UserID INT
)
AS
BEGIN
    SELECT *
    FROM    Users u INNER JOIN
            UserGroups ug ON u.UserID = ug.UserID
    WHERE   u.UserID = @UserID
END

CREATE PROCEDURE UserByName (
        @UserName VARCHAR(50)
)
AS
BEGIN
    SELECT *
    FROM    Users u INNER JOIN
            UserGroups ug ON u.UserID = ug.UserID
    WHERE   u.UserID = @UserName
END

Where you could rather create a view

CREATE VIEW UserInformation
AS
SELECT *
FROM    Users u INNER JOIN
        UserGroups ug ON u.UserID = ug.UserID

and use

SELECT  *
FROM    UserInformation
WHERE   UserID = @UserID

or

SELECT  *
FROM    UserInformation
WHERE   Name = @UserName