There is probably no point in trying to conditionaly block in SQL. The AND
is obviously integral to your query so you should check for length before
you send to SQL.
If you must check you are probably better going for a standard IF
declare @foo varchar(100)
IF LEN(@foo) > 0
BEGIN
-- DO QUERY HERE WITH AND CLAUSE
END
ELSE
BEGIN
-- DO QUERY HERE WITH NO AND CLAUSE
END
-----Original Message-----
From: [EMAIL PROTECTED]
To: SQL
Sent: 22/02/2005 23:08
Subject: Re: How 2 convert cfquery to TSQL (MS SQL stored proc)
How about doing this in TSQL?
select *
from profiles
where 0=0
<cfif form.firstname neq ''>and firstname='#form.firstname#'</cfif>
I'm having hell of a hard time getting this to work. This is how I coded
it
in TSQL but it just won't work:(
CREATE PROCEDURE dbo.sp_test
@firstname varchar(100)= 'jenna'
AS
select *
from profiles
where 0=0
IF @firstname is not NULL
and firstname= @firstname
GO
This sucker juz will NOT work:( Waaaaaaaaaaaaaaaaah:(
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Find out how CFTicket can increase your company's customer support
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49
Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2152
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
