help with where clause

• ZA_Crafty

  Ten Centuries

  Points: 1228

  More actions

  February 9, 2009 at 1:59 am

  #383894

  If you look at the stored procedure, i would like to exclude all items, if they are reffrenced in a second table. THis should be dynamic. It is used on a search screen, and I only want to exclude these one that one screen. here is what I have:

  -- Create the tables--

  create table test1 (Test1ID int, Value1 varchar(10))

  create table test2 (Test2ID int, Test1ID int, Value2 varchar(10))

  -- Create the Check Function

  Create function f_IsTest2(@Test1ID int)

  Returns Bit

  AS

  begin

  declare @b-2 bit

  if Exists (select * from Test2 where Test1ID = @Test1ID)

  begin Set @b-2 = 1 end

  Else begin Set @b-2 = 0 end

  return (@b)

  end

  --Insert some test data

  Insert Into Test1 (Test1ID, value1)

  Select 1 as Test1ID, 'abc' as Value1

  UNION ALL

  Select 2 as Test1ID, 'def' as Value1

  UNION ALL

  Select 3 as Test1ID, 'ghi' as Value1

  Insert into Test2(Test2ID, Test1ID, Value2)

  Select 1 as Test2ID, 1 as Test1ID, 'xyz' as Value2

  --Create the proc.

  Create procedure p_Test1_select

  @Test1ID int = null

  ,@IsNotInTest2 bit

  as

  Select

  *

  From

  Test1

  Where

  (@testID is null or Test1ID = @TestID)

  And????????????????????????????????????

  now those question marks should be some kind of if or case statementm but I'm completely in the dark

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  February 9, 2009 at 6:39 am

  #940426

  I am not entirely sure what you wanted, but I think this one is closer to the requirement...

  AND

  (

  (

  @IsNotInTest2 = 1

  AND Test1ID NOT IN( SELECT Test1ID FROM Test2 )

  )

  OR

  (

  @IsNotInTest2 = 0

  AND Test1ID IN( SELECT Test1ID FROM Test2 )

  )

  )

  --Ramesh
  

  ---

• ZA_Crafty

  Ten Centuries

  Points: 1228

  More actions

  February 9, 2009 at 11:56 pm

  #940992

  This looks round about right

  Thanks

  I'm going to give this a shot.

  Look at that code, add some semi colons, and you have yourself some sql flavour c# :-p