CREATE PROCEDURE [imagineDB].[sp_treatment]
	-- parameters
	@id int = 0,
	@ikeyword int = 0,
	@keyword nvarchar(100) = NULL
AS

if @id <> 0 OR @ikeyword = 1

	if @id <> 0 and @ikeyword = 0
	begin
	SET NOCOUNT ON;
	SELECT  id, pageTitle, description FROM ihcProcedures where id = @id order by pageTitle ASC
	end

	if @id = 0 and @ikeyword = 1
	begin
	SET NOCOUNT ON;
	SELECT  id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC
	end

	if @id <> 0 and @ikeyword = 1
	begin
	SET NOCOUNT ON;
	SELECT  id, pageTitle, description FROM ihcProcedures where id = @id and description like '%' + @keyword + '%' order by pageTitle ASC
	end

else

	if @id = 0 and @ikeyword = 0
	begin
	SET NOCOUNT ON;
	SELECT  id, pageTitle, description FROM ihcProcedures order by pageTitle ASC
	end

else

	if @id = 0 and @ikeyword = 1
	begin
	SET NOCOUNT ON;
	SELECT  id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC
	end

SELECT  id, pageTitle, description FROM ihcProcedures where id

quote:

---

Originally posted by Cowboy

Yes that's what I was referring to, thought it would make my code a bit neater.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

---

CREATE PROCEDURE [imagineDB].[sp_treatment]
	-- parameters
	@id int = 0,
	@ikeyword int = 0,
	@keyword nvarchar(100) = NULL
AS
SET NOCOUNT ON;
SELECT  id, pageTitle, description 
FROM ihcProcedures 
WHERE (id = @id or @id = 0)
AND  (description like '%' + @keyword + '%' OR @ikeyword = 0)
ORDER BY pageTitle ASC
GO

WHERE (id = @id or @id = 0)

quote:

---

Originally posted by Cowboy

Hi Visakh, that works, the only drawback is that it pulls back the description field when it is not used under one condition.

I am not sure I totally understand:

WHERE (id = @id or @id = 0)

how does work when it returns all the results, does it do a where 0=0?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

---

WHERE (id = 0 or 0 = 0)

quote:

---

Originally posted by Cowboy

I don't see why if the id was 0 it would not look for an id with a value of 0?

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

---

WHERE (id = 0 or 0 = 0)

quote:

---

Originally posted by Cowboy

No I don't have any o value id's, I just can't understand the logic in the OR statement I'm a simpleton

from what I understand if @id was 0 the where statement would look like this:

WHERE (id = 0 or 0 = 0)

so it wouldn't find anything but it is actually returning all the records.

I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.

---

quote:

---

It will return all records as it will evaluate second condition even if first fails as its an OR

---

quote:

---

Originally posted by Cowboy

---

quote:

---

Originally posted by Cowboy

Thanks Visakh, just another quick question would this be protected from SQL injection? I have tried '1 or '1' = '1' on my ASP page and it generates an SQL error worringly.

---

 or @id = 0

quote:

---

Originally posted by contrari4n

One problem with this solution is that it will perform an index scan.

All columns in an OR condition must be indexed to avoid this, but @id is a parameter and therefore not indexed! This means that

 or @id = 0

will force a scan of the index.


Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

---