DECLARE @sqlstatement varchar(1000) SET @sqlstatement = 'SELECT * FROM profiles WHERE 0=0'
IF @firstname IS NOT NULL SET @sqlstatement = @sqlstatement + ' AND firstname = ''' + @firstname + '''' EXEC (@sqlstatement) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 6:09 PM To: SQL 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:( ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2151 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=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
