CODEWITHSUNDEEP
sql-serverstored-procedures
Kevin Fischer
Kevin Fischer

Reputation: 352

Stored Procedure If / Then Statement

We are trying to implement a password interval, if there is one.

If sm_Setting(PasswordExpireDays) has a value, we use it. If not, continue on

CREATE Procedure user_password_date_interval_check
    @ua_pk uniqueidentifier
AS
    DECLARE @PasswordExpireDays int
    SET @PasswordExpireDays = 0

    SELECT
        sm_Setting, sm_Value
    FROM
        Setting_Misc AS sm
    INNER JOIN
        Syndicates As syn ON sm.syn_fk = syn.syn_pk
    INNER JOIN
        Company As c ON c.syn_fk = syn.syn_pk
    INNER JOIN
        User_Accounts As ua ON ua.c_fk = c.c_pk
    WHERE 
        sm.sm_Setting = 'PasswordExpireDays'
        THEN sm.sm_Value = @PasswordExpireDays

I'm having issues with the WHERE clause. I have tried CASE. Bottom line is, this row (based on a PK and Setting), I want to grab the value from the Value column.

Upvotes: 0

Views: 61

Answers (1)

Ross Bush
Ross Bush

Reputation: 15155

CREATE Procedure user_password_date_interval_check
    @ua_pk uniqueidentifier
AS
    DECLARE @PasswordExpireDays int

    SELECT
      --sm_Setting, 
      @PasswordExpireDays =COALESCE(sm_Value,0)

    FROM
      Setting_Misc AS sm
    INNER JOIN
      Syndicates As syn 
      ON sm.syn_fk = syn.syn_pk
    INNER JOIN
      Company As c
      ON c.syn_fk = syn.syn_pk
    INNER JOIN
      User_Accounts As ua
      ON ua.c_fk = c.c_pk

    WHERE sm.sm_Setting = 'PasswordExpireDays'


    --@PasswordExpireDays is either default 0 or the value from the table if not null.

Upvotes: 3

Related Questions