CODEWITHSUNDEEP
sqlsql-serverstored-procedures
john
john

Reputation: 11669

Add if else condition in stored procedures and return hard coded values?

I am working with stored procedures in SQL Server for the first time. Below is the existing stored procedure I have:

USE [Hello]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Profile] 
    (@Culture AS VARCHAR(10))
AS
BEGIN
    SELECT 
        lt.ID AS TypeId,
        ltt.Title AS TypeTitle
    FROM 
        Hello..lkpLocationType lt
    INNER JOIN 
        Hello..TypeText ltt ON ltt.TypeId = lt.ID
                            AND ltt.Culture = @Culture
    WHERE  
        lt.IsActive = 1                    
END

Now I want to add one more condition here so I need to have if and else condition here.

Is this possible to do directly in stored procedure by hardcoding these three values A, B and C and return if licenseId value is 9? So looks like I need to modify this alter procedure and licenseId as well? Am I right?

ALTER PROCEDURE [dbo].[Profile] 
    (@Culture AS VARCHAR(10)
     @LicenseId AS INT)

If this is not possible to do then I can execute another SQL if licenseId value is 9 which can return A, B and C?

Update: That makes sense now. There is one thing I am confuse now. My VB code is executing this stored procedure and after that it is iterating the values returned by my existing original SQL by extracting value of two columns TypeTitle and TypeID.

Dim dtLocationTypes As DataTable = dalClientProfile.Profile(Session("Culture"), Session("LicenseID"))
If dtLocationTypes.Rows.Count > 0 Then
    ddlLocationType.DataSource = dtLocationTypes
    ddlLocationType.DataTextField = "TypeTitle"
    ddlLocationType.DataValueField = "TypeID"
    ddlLocationType.DataBind()
End If

Above code was working fine before but when I added LicenseID so it is executing the If block now in the stored procedure but since we dont have these two columns now in the returnd result so I believe it is not able to extract value from them. Is there any way we can modify stored procedure for that If condition so that while iterating it works fine in the VB code?

In general this is the output for SQL which is in ELSE block and that's how my VB code is able to extract it.

TypeID  TypeTitle
-----------------
1       Hello
2       World
3       PQR

Now for the SQL which is in IF block, I want to return something like this directly in stored procedure if possible?

TypeID  TypeTitle
-----------------
1       A
2       B
3       C

Upvotes: 0

Views: 1879

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can use try this :

ALTER PROCEDURE [dbo].[Profile] 
       (
              @Culture AS VARCHAR(10),
              @LicenseId as int
       )
AS
BEGIN
        IF @LicenseId = 9
        BEGIN 
            SELECT '1' as TypeId ,'A' as TypeTitle
            union
            SELECT '2' as TypeId ,'B' as TypeTitle
            union
            SELECT '3' as TypeId ,'C' as TypeTitle
        END
        ELSE
        BEGIN
            SELECT lt.ID AS TypeId,
                      ltt.Title AS TypeTitle
            FROM Hello..lkpLocationType lt
            INNER JOIN Hello..TypeText ltt
                    ON ltt.TypeId = lt.ID
                    AND ltt.Culture = @Culture
            WHERE  lt.IsActive = 1     
        END
END

It will give you the expected output for the IF block

TypeID  TypeTitle
-----------------
1       A
2       B
3       C

Upvotes: 0

qxg
qxg

Reputation: 7036

Do you want something like

SELECT *
FROM (VALUES (1, 'A'), (2, 'B'), (3, 'C')) AS T(TypeId, TypeTitle)

Full pseudo code

IF @LicenseId = 9
BEGIN
    SELECT *
    FROM (VALUES (1, 'A'), (2, 'B'), (3, 'C')) AS T(TypeId, TypeTitle)
END
ELSE
BEGIN
    Original stored procedure
END

BTW, can you implemented it in UI layer as it looks like a UI only requirement?

Upvotes: 1

Akash KC
Akash KC

Reputation: 16310

Yes, you can have if...else condition in stored procedure :

ALTER PROCEDURE [dbo].[Profile] 
       (
              @Culture AS VARCHAR(10),
              @LicenseId as int
       )
AS
BEGIN
        IF @LicenseId = 9
        BEGIN 
            SELECT 'A','B','C'
        END
        ELSE
        BEGIN
            SELECT lt.ID AS TypeId,
                      ltt.Title AS TypeTitle
            FROM Hello..lkpLocationType lt
            INNER JOIN Hello..TypeText ltt
                    ON ltt.TypeId = lt.ID
                    AND ltt.Culture = @Culture
            WHERE  lt.IsActive = 1     
        END
END

For testing, I've created SQLFIDDLE where you can see result of following stored procedure :

CREATE PROCEDURE [dbo].[Profile] 
       (
              @LicenseId as int
       )
AS
BEGIN
        IF @LicenseId = 9
        BEGIN 
            SELECT 'A','B','C'
        END
        ELSE
        BEGIN
           SELECT 'A','B'
        END
END

Upvotes: 1

Related Questions