CODEWITHSUNDEEP
sqlfunctioncase
Richard Nixon
Richard Nixon

Reputation: 169

Turn a SQL case statement into stored procedure?

I have a very large script which is creating multiple views. In a number of the views there is the same piece of script being used. This script is listed below.

CASE WHEN pc.[user_name] IN
(
    SELECT [user_name]
    FROM people AS p WITH(NOLOCK) 
    WHERE p.status_id = 1 p.last_login > DATEADD(MONTH, -12, GETDATE())
    AND p.[user_name] NOT IN 
    (
        SELECT p.[user_name]
        FROM people AS p WITH(NOLOCK)
        WHERE p.status_id IN (1,2) AND p.[user_name] LIKE '%2'
    )
) THEN pc.[user_name]
ELSE 'standarduser' END AS created_by

Could someone point me in the right direction of how to write a function where I can pass in pc.[user_name] and it will return the correct value? I am new to functions in SQL. Thank you.

Upvotes: 2

Views: 686

Answers (3)

GarethD
GarethD

Reputation: 69789

The function can be created as follows:

CREATE FUNCTION [dbo].[FunctionName] (@UserName VARCHAR(50)) 
RETURNS VARCHAR(50) 
AS
BEGIN   
    RETURN  COALESCE(
                (   SELECT  [User_name]
                    FROM    people AS p WITH(NOLOCK)      
                    WHERE   p.status_id = 1 
                    AND     p.last_login > DATEADD(MONTH, -12, GETDATE())     
                    AND     p.[user_name] NOT LIKE '%2'
                    AND     [User_name] = @UserName
                ), 'StandardUser')
END

I have tried to simplify your select as much as possible based on the assumption that user_name is unique, i.e. a user_name cannot have both a status_ID of 1 and 2, this allowed me to remove a NOT IN from the statement, as you are first selecting a group of users where the status_ID is 1 then removing from them where the status_ID is 1 or 2 and the user name ends in 2. Since there will be be nobody in the first group who does not have a status_ID of 1, you can then just remove those in the first group whose username ends in 2, which doesn't require a subquery, just a where clause.

Having said all that, I would still be inclined to use a view or similar set based solution to achieve the same result. Something like:

SELECT  *, COALESCE(a.[User_Name], 'StandardUser') [NewUserName]
FROM    People p
        LEFT JOIN 
        (   SELECT  [User_name]
            FROM    people AS p WITH(NOLOCK)      
            WHERE   p.status_id = 1 
            AND     p.last_login > DATEADD(MONTH, -12, GETDATE())     
            AND     p.[user_name] NOT LIKE '%2'
        ) a
            ON p.[User_name] = a.[User_name]

Upvotes: 2

JeffO
JeffO

Reputation: 8043

You can make this a view or table function (if you need othere logic or want to pass in a parameter) and include it in other views/procs joined like a table

;
with users as (
select p.user_name
  , p.status_id
  , p.last_login
from people as p with(nolock)
where p.status_id IN (1,2)
)
select DISTINCT u.user_name
  , CASE WHEN u.[user_name] IN 
     ( 
       SELECT [user_name] 
       FROM users AS u1 
       WHERE u1.status_id = 1 u1.last_login > DATEADD(MONTH, -12, GETDATE()) 
       AND u1.[user_name] NOT IN  
          ( 
             SELECT u2.[user_name] 
             FROM users AS u2  
             WHERE u2.status_id IN (1,2) AND u2.[user_name] LIKE '%2' 
          ) 
    ) THEN u.[user_name] 
      ELSE 'standarduser' END AS created_by 
from users as u

Upvotes: 0

Mikey Mouse
Mikey Mouse

Reputation: 3098

You could try a Scalar valued function like this

CREATE function [dbo].[MyFunction] (
@UserName nvarchar
) 
returns nvarchar
as
begin
CASE WHEN pc.[user_name] IN 
(     
    SELECT [user_name]     
    FROM people AS p WITH(NOLOCK)      
    WHERE p.status_id = 1 p.last_login > DATEADD(MONTH, -12, GETDATE())     

    AND p.[user_name] NOT IN 
    (         
        SELECT p.[user_name]         
        FROM people AS p WITH(NOLOCK)         
        WHERE p.status_id IN (1,2) AND p.[user_name] LIKE '%2'     
    )
)
THEN pc.[user_name] ELSE 'standarduser' END AS created_by 

end

GO

Edit: Oh, assuming you're using SQL Server that is

Upvotes: 0

Related Questions