CODEWITHSUNDEEP
sqlsql-serverstored-procedures
SearchForKnowledge
SearchForKnowledge

Reputation: 3751

How to use if/else in SQL Server stored procedure

I have the following stored procedure:

ALTER PROCEDURE [dbo].[Provider]
(@strPro varchar(200))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * 
    FROM [db1].[dbo].[table1]
    WHERE [Name] = @strPro
END

I have a dropdownlist which will have the following entry:

All People
John D.
Mike K.
Alan H.
Paul Q.

How can I modify the stored procedure so that if the user selects All People the WHERE statement will be a LIKE instead of a = sign

Example:

ALTER PROCEDURE [dbo].[Provider]
(@strPro varchar(200))
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * 
    FROM [db1].[dbo].[table1]

    if @strPro = 'All People'
    BEGIN
        WHERE [Name] LIKE '%'
    END
    ELSE
    BEGIN
        WHERE [Name] = @strPro
    END
END

Upvotes: 1

Views: 112

Answers (2)

BWS
BWS

Reputation: 3846

try something like this, where you build the SQL statement in the Stored Procedure:

ALTER PROCEDURE [dbo].[Provider]
(
    @strPro varchar(200)
)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @SQL varchar(2000)

    -- Insert statements for procedure here
    SET @SQL = 'SELECT * FROM [db1].[dbo].[table1] '

    if @strPro <> 'All People'
    BEGIN
        SET @SQL = @SQL + ' WHERE [Name] = ''' + @strPro + '''
    END

    EXEC (@SQL)

END

Note1: You won't need a WHERE clause if you want 'All People' ... Note2: you may have to adjust some of the single quotes ...

Upvotes: 1

Yuriy Galanter
Yuriy Galanter

Reputation: 39807

Like this

SELECT * FROM [db1].[dbo].[table1]
WHERE @strPro = 'All People' OR [Name] = @strPro

If "All People" is passed - first condition will be true and all records will be returned (You don't need to do LIKE '%') otherwise only records with matched [Name] will be returned.

Upvotes: 7

Related Questions