Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    111

    Unanswered: Building a query in a stored proc with string

    This should be an easy enough answer to find if I just knew what to search on!

    I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says:
    select * from tblNames where FirstName = 'Hannah'
    or
    select * from tblNames where LastName = 'Montana'
    or
    select * from tblNames where FirstName = 'Hannah' and LastName='Montana'

    My problem is putting the single quotes around the variable value.
    SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it

    Thanks

  2. #2
    Join Date
    Jul 2006
    Posts
    111
    @whereClause + 'calls.phone=''' + @InputPhoneNumber + ''''

    Count them carefully!

    Thanks.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I really prefer:
    Code:
    DECLARE @LastName		NVARCHAR(50)
    SELECT @LastName = 'Montana'
    SELECT @LastName, ' AND tblNames.LastName = ' + QuoteName(@LastName, '''')
    SELECT @LastName = 'O''Rourke'
    SELECT @LastName, ' AND tblNames.LastName = ' + QuoteName(@LastName, '''')
    It saves my posterior when the user's have apostrophes in their last names!

    -PatP

  4. #4
    Join Date
    Feb 2007
    Posts
    62
    This should be an easy enough answer to find if I just knew what to search on!
    You should search on "SQL Injection".

    Seriously - this is a bad idea for many reasons. SQL injection is one of them. Another is that you build unique queries in every case. It all needs to be parsed, planned etc which takes CPU time and limits scalability.

    There are other ways of doing this, e.g.
    • Multiple SPs for different sets of conditions
    • IF statements in a single SP
    • ... where (x=@y or @y is null)

    There are pros & cons of the various methods but dynamic SQL is the worst.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •