Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-11-02
3
Medium Priority
?
271 Views
Last Modified: 2012-05-05
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

Please alter the code below:

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)
0
Comment
Question by:quest_capital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 11

Expert Comment

by:rw3admin
ID: 17862706
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)
0
 

Author Comment

by:quest_capital
ID: 17862826
rw3admin

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

0
 
LVL 11

Accepted Solution

by:
rw3admin earned 2000 total points
ID: 17862860
:)
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)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question