Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing options to Stored procedure

Posted on 2011-09-14
3
Medium Priority
?
214 Views
Last Modified: 2012-05-12
Hi There,

My stored procedure takes two parameters from the application, options and amount.
@option can be = or > or <.

How can I take the @option value and replace in the where condition?


USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[search]


      @option varchar,
      @Amount Decimal

      
AS

      

SELECT
      [Search].[Id],
      [Search].[Amount],

FROM
      [dbo].[Search]
      WHERE
      (
      
            [Search].[Amount] @Option @Amount
      
            --i.e  [Search].[Amount] > @Amount


      )
0
Comment
Question by:theartha
  • 2
3 Comments
 
LVL 18

Expert Comment

by:lludden
ID: 36536723
You can use dynamic SQL

ALTER PROCEDURE [dbo].[search]
      @option varchar,
      @Amount Decimal
AS
DECLARE @SQL varchar(max) =
'SELECT
      [Search].[Id],
      [Search].[Amount],

FROM
      [dbo].[Search]
      WHERE  [Search].[Amount] ' +  @Option  + CAST(@Amount as varchar(20))
     
EXECUTE (@SQL)
0
 
LVL 18

Accepted Solution

by:
lludden earned 1000 total points
ID: 36536752
If you are not comfortable with dynamic SQL (I am not without very well washed inputs), you can do this:
ALTER PROCEDURE [dbo].[search]
      @option varchar,
      @Amount Decimal
AS
SELECT
      [Search].[Id],
      [Search].[Amount],
FROM
      [dbo].[Search]
      WHERE (@Option = '=' AND Search.Amount = @Amount)
OR
      (@Option = '<' AND Search.Amount < @Amount)
OR
      (@Option = '>' AND Search.Amount > @Amount)

     
     
            [Search].[Amount] @Option @Amount
     
            --i.e  [Search].[Amount] > @Amount


      )
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 1000 total points
ID: 36536776
You don't want to use dynamic SQL as that opens up security issues.  You could do something like this...
SELECT
   ID
   Amount
FROM
   Search
WHERE
   (Amount = @amount AND @option = 'Equal)
   OR (Amount > @amount AND @option = 'Greater')
   OR (Amount < @amount AND @option = 'Less')

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

916 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