CODEWITHSUNDEEP
sql-serverstored-procedures
Jackal
Jackal

Reputation: 3521

Return table with different columns based on parameters SQL

I'm writing a stored procedure that runs different selects based on the user role , this is just an example to showcase my intention since i have a lot of roles to work with.

IF OBJECT_ID('dbo.spSelectArtigos') IS NOT NULL
DROP PROCEDURE spSelectArtigos
GO
CREATE PROCEDURE spSelectArtigos 
    @IdUser int,
    @Acesso nvarchar(20)

AS
BEGIN
    IF(@Acesso = 'User')
        SELECT col1,col2 from table1 where IdUser = @IdUser
    IF(@Acesso = 'Logistica')
        SELECT col1,col2,col3 from table1 where IdUser = @IdUser  
    IF(@Acesso = 'Admin')
        SELECT * From table1 
END

Is there a more effective way to do this with less code using some sort of logic?

Upvotes: 0

Views: 800

Answers (2)

Ross Bush
Ross Bush

Reputation: 15155

Here is a kludgy way but it is concise.

IF OBJECT_ID('dbo.spSelectArtigos') IS NOT NULL
DROP PROCEDURE spSelectArtigos
GO
CREATE PROCEDURE spSelectArtigos 
    @IdUser int,
    @Acesso nvarchar(20)

AS
BEGIN
    DECLARE @Result TABLE(A INT,B INT,C INT,D INT)
    INSERT @Result SELECT A,B,C,D FROM table1 WHERE idUser=@idUser

    IF(@Acesso = 'User')
        UPDATE @Result SET C=NULL, D=NULL 
    IF(@Acesso = 'Logistica')
        UPDATE @Result SET D=NULL 
    SELECT * From @Result 
END

Another way but dealing with ID's

SELECT 
    A=CASE WHEN @Acesso >= 10 THEN A ELSE NULL END,
    B=CASE WHEN @Acesso >= 20 THEN B ELSE NULL END,
    C=CASE WHEN @Acesso >= 99 THEN C ELSE NULL END
FROM 
    table1 
WHERE 
    idUser=@idUser

Upvotes: 1

Doug Coats
Doug Coats

Reputation: 7107

IDK why i added a check to make sure the user actually exists but it isnt a bad idea 

IF OBJECT_ID('dbo.spSelectArtigos') IS NOT NULL
DROP PROCEDURE spSelectArtigos
GO
CREATE PROCEDURE spSelectArtigos 
    @IdUser int,
    @Acesso nvarchar(20)

AS
BEGIN
    CASE 
        WHEN @Acesso ='User' 
            AND
                EXISTS (SELECT * FROM table1 WHERE IdUser = @IdUser)
            THEN 
              SELECT col1,col2 from table1 where IdUser = @IdUser

        WHEN @Acesso ='Logistica' Then
            AND
                EXISTS (SELECT * FROM table1 WHERE IdUser = @IdUser)
            THEN        
              SELECT col1,col2,col3 from table1 where IdUser = @IdUser  

        WHEN @Acesso ='Admin'
            AND
                EXISTS (SELECT * FROM table1 WHERE IdUser = @IdUser)
            THEN        
              SELECT * From table1 
    END
END

Upvotes: 1

Related Questions