You need to store the whole thing in a string variable such as :
DECLARE @YourVar varchar(4000)
SET @YourVar = ' SELECT * FROM Table WHERE 1 = 1'
If a = 1
SET @YourVar = @YourVar + ' AND ColumnA = 10'
If thisvar = 'No'
SET @Yourvar = @YourVar + '> AND ColumnB IN (#A_List#)'
If OrderBy = 'Yes'
SET @Yourvar = @YourVar + ' ORDER BY ColumnC'
EXEC(@YourVar)
There is no end if in Sp's If you have several things you wish to do you
need to enclose them in a BEGIN....END i.e.
If YourCondisition
BEGIN
Do lots of stuff
END
Else
BEGIN
Do lots of other stuff
END
-----Original Message-----
From: Allan Cliff [mailto:[EMAIL PROTECTED]
Sent: 21 October 2003 08:47
To: CF - List
What is the SP equivalent of:
SELECT *
FROM Table
WHERE 1 = 1
<CFIF a IS 1>AND ColumnA = 10</CFIF>
<CFIF thisvar IS "No"> AND ColumnB IN (#A_List#)</CFIF>
<CFIF orderby IS "Yes">ORDER BY ColumnC</CFIF>
Not this actual example but the principle as I can't see closing IFs in SPs.
--
** Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
For human help, e-mail: [EMAIL PROTECTED]
