You will need to pass in @firstname to the sp as an input parameter (or declare it in the SP but that way will not allow you to pass in a dynamic value).
Also you should use sp_executesql command to execute your dynamic SQL (if you really insist on using dynamic SQL! ;-) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 February 2005 23:52 To: SQL Subject: Re: How 2 convert cfquery to TSQL (MS SQL stored proc) Hi, I tried this, but it doesn't work because its looking for the @firstname var When I add the @firstname varchar(100)='Jenna' it doesn't work. This sure is quircky stuff, not intuitive like CF:( ----- Original Message ----- From: "Chip Griffin" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Tuesday, February 22, 2005 3:22 PM Subject: RE: How 2 convert cfquery to TSQL (MS SQL stored proc) > 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:( > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:2158 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
