Solved

Passing options to Stored procedure

Posted on 2011-09-14
3
204 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 250 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 250 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Insert where not exists 24 48
replicated - directional or bidirectional? 3 36
Need help how to find where my error is in UFD 6 40
SQL trigger 5 26
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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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