Try Passing a variable across that it will NEVER be. The variable will just need to be referenced in both places. Not the best way, but it'll work.
Ron -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 22, 2005 10:20 AM To: SQL Subject: RE: How 2 convert cfquery to TSQL (MS SQL stored proc) I'm not sure why... I have a big IF in the stored proc that makes sure the variables is not equal to "". It should work... Is the FirstName coming across as a space " "? If so, adjust my stored proc to check for that. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, February 21, 2005 17:27 To: SQL Subject: RE: How 2 convert cfquery to TSQL (MS SQL stored proc) Tim - Whenever I try this, it fails testing when the @FirstName is ''. My SQL Server 2000 will not allow that value. My next choice is making the @FirstName optional. It appears that is possible, but (again) it does not work on my SQL Server 2000. -brian > You want something like this (not checked, so syntax may be off a > little): > > <cfset Variables.FirstName = ""> > <cfif IsDefined("Form.FirstName")> > <cfset Variables.FirstName = Form.FirstName> > </cfif> > > <cfset Variables.ZipsReturned = ""> > <cfif IsDefined("Form.ZipsReturned ")> > <cfset Variables.ZipsReturned = Form.ZipsReturned > > </cfif> > > <cfquery datasource="blah" name="blahquery"> > FindProfiles '#Variables.FirstName#', '#Variables.ZipsReturned#' > </cfquery> > > Then, in your database, create this stored procedure by copy/pasting the > following into a Query Analyzer or other such window: > > create procedure FindProfiles > @FirstName varchar(255), > @ZipsReturned varchar(8000) > > as > > set nocount on > > declare @SQL varchar(8000), @WhereClause varchar(8000) > > set @SQL = 'select * from Profiles > set @WhereClause = '' > > if @FirstName > '' > begin > if @WhereClause > '' set @WhereClause = @WhereClause = ' > and ' > set @WhereClause = @WhereClause + 'FirstName = ''' + > @FirstName + '''' > end > > if @ZipsReturned > '' > begin > if @WhereClause > '' set @WhereClause = @WhereClause = ' > and ' > set @WhereClause = @WhereClause + 'Zipcode in (' + > @ZipsReturned + ')' > end > > if @WhereClause > '' set @SQL = @SQL + ' where ' + @WhereClause > > execute (@SQL) > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Now there’s a better way to fax. eFax makes it possible to use your existing email account to send and receive faxes. Try eFax free. http://www.houseoffusion.com/banners/view.cfm?bannerid=63 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2141 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
