How to use if/else in SQL Server stored procedure

Question

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

Comfortably Numb · Accepted Answer · 2015-01-21 21:19:25Z

7

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.

answered Jan 21, 2015 at 21:19

Comfortably Numb

39.9k17 gold badges82 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SearchForKnowledge Over a year ago

All People != John D.... for example. Sorry let me clarify, if All People is selected return all of the list not just All People.

BWS Over a year ago

I think you may have missed the explanation in the answer ... this will work the way it is described in the original question.

Jim Over a year ago

SearchForKnowledge - [Name] LIKE '%' is equivalent to @strPro = 'All People' in this case. Both queries return all the rows when "All People" is selected.

Comfortably Numb Over a year ago

@SearchForKnowledge Try it in action. See if it behaves as desired.

SearchForKnowledge Over a year ago

You know what, it is late in the day and my brain is fried... you are right :) It wasn't supposed to be rude although maybe it sounded like it. My apology.

BWS · Accepted Answer · 2015-01-21 21:35:06Z

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 ...

Collectives™ on Stack Overflow

How to use if/else in SQL Server stored procedure

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related