We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# I need to add a parameter to include or exclude a "WHERE" cluase

on
Medium Priority
287 Views
The code below works fine, it select the complete table I want to add another parameter.
If @Lock = 'on' then add the where cluase (WHERE t9 = 1)
else
If @Lock = '' then don't add the where cluase

ALTER PROCEDURE [dbo].[Events]
@Order varchar(50) = 't5',
@By varchar(10) = 'Asc'
AS
Declare @sql varchar(500)

SET @sql='SELECT * FROM tbl1 ORDER BY '+@Order+' '+@By+''
exec(@sql)
Comment
Watch Question

## View Solution Only

Commented:
ALTER PROCEDURE [dbo].[Events]
@Order varchar(50) = 't5',
@By varchar(10) = 'Asc'
@lock varchar(2)='on'
AS
Declare @sql varchar(500),
@Where Varchar(500)
Select @Where=''

If @Lock = 'on' then begin Set @WHERE=' Where t9 = 1'

SET @sql='SELECT * FROM tbl1 '+@WHERE +'ORDER BY '+@Order+' '+@By+''
exec(@sql)

Commented:

I get some error:
I found one: see below

ALTER PROCEDURE [dbo].[Events]
@Order varchar(50) = 't5',
@By varchar(10) = 'Asc'<<<<<<<<<< error (,)
@lock varchar(2)='on'
AS
Declare @sql varchar(500),
@Where Varchar(500)
Select @Where=''

If @Lock = 'on' then begin Set @WHERE=' Where t9 = 1'

SET @sql='SELECT * FROM tbl1 '+@WHERE +'ORDER BY '+@Order+' '+@By+''
exec(@sql)

Error Message
Msg 156, Level 15, State 1, Procedure Events, Line 10
Incorrect syntax near the keyword 'then'.
Msg 102, Level 15, State 1, Procedure Events, Line 14
Incorrect syntax near ')'.

Commented:
:)
you even pointed out one of the error, I was missing a comma
here is the correct one
ALTER PROCEDURE [dbo].[Events]
@Order varchar(50) = 't5',
@By varchar(10) = 'Asc',
@lock varchar(2)='on'
AS
Declare @sql varchar(500),
@Where Varchar(500)
Select @Where=''

If @Lock = 'on' begin Set @WHERE=' Where t9 = 1' End

SET @sql='SELECT * FROM tbl1 '+@WHERE +'ORDER BY '+@Order+' '+@By+''
exec(@sql)

Not the solution you were looking for? Getting a personalized solution is easy.

##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile