SET @ISBN = '%' + @ISBN + '%'
SET @TITLE= '%' + @TITLE+ '%'
SET @AUTHOR= '%' + @AUTHOR+ '%'

SELECT ISBN, TITLE, AUTHOR
  FROM BOOKS
  WHERE
    ISBN LIKE @ISBN OR
    TITLE LIKE @TITLE OR
    AUTHOR LIKE @AUTHOR

quote:

---

Originally posted by Srinika

Create ur "Where clause" Seperately and use Dynamic SQL

eg.

...


if @ISBN is not null
Set @Where = ' ISBN LIKE @ISBN OR'
.....
.....

Set @Where = ' Where ' + @Where + ' 1=0'

Execute ('SELECT ISBN, TITLE, AUTHOR FROM BOOKS ' + @Where )



Srinika

---

quote:

---

I need to do this as a stored proc as I need to help prevent SQL injections, and for the same reason I really dont want to compose a string of the SELECT command and EXECUTE it within the SP

---

quote:

---

Originally posted by RyanRandall

Wow, you managed to write all that and I still don't see an answer to a)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

---

SELECT ISBN, TITLE, 
  CASE WHEN AUTHOR='' THEN 'Not Specified' ELSE AUTHOR END AS AUTHOR,
  CASE WHEN PUBLISHER='' THEN 'Not Specified' ELSE PUBLISHER END AS PUBLISHER,
  FROM BOOKS 
  WHERE (
    ISBN LIKE CASE WHEN @Isbn='' THEN NULL ELSE '%'+@Isbn+'%' END OR
    TITLE LIKE CASE WHEN @Title='' THEN NULL ELSE '%'+@Title+'%' END OR
    AUTHOR LIKE CASE WHEN @Author='' THEN NULL ELSE '%'+@Author+'%' END OR
    PUBLISHER LIKE CASE WHEN @Publisher='' THEN NULL ELSE '%'+@Publisher+'%' END OR
    REVIEWS LIKE CASE WHEN @ReviewTxt='' THEN NULL ELSE '%'+@ReviewTxt+'%' END
  )
  AND TITLE IS NOT NULL

  IF @L_operator='OR'  BEGIN
    -- select statement for OR
    SELECT ISBN, TITLE, 
      CASE WHEN AUTHOR='' THEN 'Not Specified' ELSE AUTHOR END AS AUTHOR,
      CASE WHEN PUBLISHER='' THEN 'Not Specified' ELSE PUBLISHER END AS PUBLISHER
      FROM BOOKS 
      WHERE (
        ISBN LIKE CASE WHEN @Isbn<>'' THEN '%'+@Isbn+'%' ELSE NULL END OR
        TITLE LIKE CASE WHEN @Title<>'' THEN '%'+@Title+'%' ELSE NULL END OR
        AUTHOR LIKE CASE WHEN @Author<>'' THEN '%'+@Author+'%' ELSE NULL END OR
        PUBLISHER LIKE CASE WHEN @Publisher<>'' THEN '%'+@Publisher+'%'  ELSE NULL END OR
        REVIEWS LIKE CASE WHEN @ReviewTxt<>'' THEN '%'+@ReviewTxt+'%'  ELSE NULL END
      )
      AND TITLE IS NOT NULL
  END
  ELSE BEGIN
    -- select statement for AND
    SELECT ISBN, TITLE, 
      CASE WHEN AUTHOR='' THEN 'Not Specified' ELSE AUTHOR END AS AUTHOR,
      CASE WHEN PUBLISHER='' THEN 'Not Specified' ELSE PUBLISHER END AS PUBLISHER
      FROM BOOKS 
      WHERE (
        ISBN LIKE CASE WHEN @Isbn<>'' THEN '%'+@Isbn+'%' ELSE '%%' END AND
        TITLE LIKE CASE WHEN @Title<>'' THEN '%'+@Title+'%' ELSE '%%' END AND
        AUTHOR LIKE CASE WHEN @Author<>'' THEN '%'+@Author+'%' ELSE '%%' END AND
        PUBLISHER LIKE CASE WHEN @Publisher<>'' THEN '%'+@Publisher+'%'  ELSE '%%' END AND
        REVIEWS LIKE CASE WHEN @ReviewTxt<>'' THEN '%'+@ReviewTxt+'%'  ELSE '%%' END
      )
      AND TITLE IS NOT NULL
  END

quote:

---

Originally posted by Jakes Daddy
I am however intrigued by the suggestions to use dynamic sql - build a string of the SQL I want to run, then call it with EXECUTE

I was under the impression that this is generally a bad idea ??? I want to prevent SQL injection attacks, and isn't there something about SQL execution plans or something ???

As you can tell, I am a complete novice with this stuff, and I bow to your superior knowledge. I would just be interested to a) hear why dsql was suggested and b) if my concerns about dsql are valid ?

Thanks again

Nathan

---