I just wrote this off the top of my head, so it may not run (I'm not sure
about the double and single quotes), but it gives you some idea of how to
dynamically build SQL statements in a stored proc (if that's what you're
looking for):
CREATE PROC MyProc
@Active bit = NULL,
@Test bit = NULL
AS
DECLARE @sSQL as varchar(1000)
SET @sSQL = 'Select * from member where membernumber <> 0'
IF @Active = 1 SET @sSQL = @sSQL + 'and active="y"'
IF @Test = 1 SET @sSQL = @sSQL + 'and test="no"'
SET @sSQL = @sSQL + 'order by membernumber'
EXEC (@sSQL)
GO
-----Original Message-----
From: William J Wheatley [mailto:[EMAIL PROTECTED]]
Sent: January 9, 2001 3:47 PM
To: CF-Talk
Subject: RE: OT STORED PROCEDURES
is there anyway to do the if in the query of a stored procedure without
using a Cursor.
and this is what i wanted
Select *
from member
where membernumber<>0
and active='y'
and test='no'
order by membernumber
by using this type logic
select *
from member
where membernumber<>0
IF @active is 'y'
and active='y'
END
IF @test is 'y'
and test='no'
END
order by membernumber
so that by using a stored procedure i can say is active is N then the (and
active='y') wont run properly.
=)
hope that helps
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
