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)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:2132
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
