Your DELETE #temp1 statements are dangerous.

If a record does not equal to #ID it still might equal #ClassID or sP column.

So you have two diffferents sets of business rules here.

1) All three columns must match

2) At least one column must match

CREATE PROCEDURE dbo.s_Risk

(

@ID VARCHAR(200),

@sp-2 VARCHAR(50),

@ClassID VARCHAR(25)

)

AS

SET NOCOUNT ON

SELECT@ID = NULLIF(@ID, ''),

@sp-2 = NULLIF(@sP, ''),

@ClassID = NULLIF(@ClassID, '')

CREATE TABLE#ID

(

ID VARCHAR(200)

)

SET@ID = ',' + @ID + ','

INSERT#ID

SELECTDISTINCT

SUBSTRING(@ID, Number + 1, CHARINDEX(',', @ID, Number + 1) - Number - 1)

FROMTally

WHERENumber < DATALENGTH(@ID)

AND SUBSTRING(@ID, Number, 1) = ','

CREATE TABLE#pForm

(

sP VARCHAR(50)

)

SET@sp-2 = ',' + @sp-2 + ','

INSERT#pForm

SELECTDISTINCT

SUBSTRING(@sP, Number + 1, CHARINDEX(',', @sp-2, Number + 1) - Number - 1)

FROMTally

WHERENumber < DATALENGTH(@sP)

AND SUBSTRING(@sP, Number, 1) = ','

CREATE TABLE#ClassID

(

Class INT

)

SET@ClassID = ',' + @ClassID + ','

INSERT#ClassID

SELECTDISTINCT

SUBSTRING(@ClassID, Number + 1, CHARINDEX(',', @ClassID, Number + 1) - Number - 1)

FROMTally

WHERENumber < DATALENGTH(@ClassID)

AND SUBSTRING(@ClassID, Number, 1) = ','

SELECTr.ID,

r.sP,

r.ClassID,

r.MoneyValue,

r.NetAmount,

r.Datestarted,

r.DateEnded

FROMRisk AS r

LEFT JOIN#ID

IF @ID IS NULL AND @sp-2 IS NULL AND @ClassID IS Null

SELECTID,

sP,

ClassId,

MoneyValue,

NetAmount,

Datestarted,

DateEnded

FROMRisk

ELSE

SELECTr.ID,

r.sP,

r.ClassId,

r.MoneyValue,

r.NetAmount,

r.Datestarted,

r.DateEnded

FROMRisk AS r

WHEREEXISTS (SELECT * FROM #ID AS i WHERE i.ID = r.ID)

OR EXISTS (SELECT * FROM #pForm AS p WHERE p.sP = r.sP)

OR EXISTS (SELECT * FROM #ClassID AS c WHERE c.ClassID = r.ClassID)

You can very easy change this query and replace OR EXISTS to AND EXISTS if that's your business rules.