February 9, 2009 at 1:59 am
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
February 9, 2009 at 6:39 am
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
February 9, 2009 at 11:56 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply