Hi All,Herewith my very 1st post...I have a situation where I need to use a flag in a proc to either enable or disable 1 line in the where clause. The actual version has about 6 or 7 joins with 9 parameters, but for nowNow a VERY basic version follows...CREATE TABLE [Test] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [TName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Descr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [IsActive] [tinyint] NOT NULL CONSTRAINT [DF_Location_IsActive] DEFAULT (1), [IsSystem] [bit] NOT NULL CONSTRAINT [DF_Location_IsSystem] DEFAULT (0), [IsDepo] [bit] NOT NULL CONSTRAINT [DF_Location_IsDepo] DEFAULT (0), ) INSERT INTO [Test] ( [TName] , [Descr] , [IsActive] , [IsSystem] , [IsDepo] )VALUES ( 'Warehouse' , 'On Site Warehouse' , 1 , 0 , 0 )INSERT INTO [Test] ( [TName] , [Descr] , [IsActive] , [IsSystem] , [IsDepo] )VALUES ( 'CapeTown' , 'CPT Warehouse' , 1 , 0 , 1 )INSERT INTO [Test] ( [TName] , [Descr] , [IsActive] , [IsSystem] , [IsDepo] )VALUES ( 'Durban' , 'Harbour Holding' , 1 , 0 , 0 )GOCreate procedure sp_test1 @TName varchar(50) ,@ReturnAll as bit as--exec sp_test1 @TName='Durban', @ReturnAll = 0--exec sp_test1 @TName=null, @ReturnAll = 1select * from Testwhere TName = @Tname
The idea is that if you send a 1 value to the bit parameter, then the procedure should run without a where clause.One way i thought of doing it is to have 2 seperate code blocks. Then you test in the begining of the proc for the bit. if true, you exec the one block, if false the other. Then the only difference between the 2 code blocks is the where clause.But in my mind this does not seem like best practice. I'm sure there is a better way to do this.I just started this job, and I still eed to impress them... ;-)Only some can learn from other peoples mistakes. The rest of us has to be the other people...