SQL Stored Proc question

[Swd1974]

I have a rather large cfquery slowing me down. i can build simple stored procs
but I dont know anything advanced.

Can you take a cfquery like this

<cfquery name="getname" datasource="dsn">
SELECT name, address, zipcode, city, state,etc,etc,etc
FROM users
WHERE 0=0
<cfif form.name IS NOT "">AND name = '#form.name#'</cfif>
<cfif form.zipcode IS NOT "">IN zipcode =
'#form.zipcode#'</cfif>
<cfif form.cityIS NOT "">IN city = '#form.city#'</cfif>
</cfquery>

I have like 15 if's in trhe statement. Is this feasible in SQL stored procs or
will it actually hurt performance more?

[philh]

Hi SWD1974,

Moving data-centric actions into the data bubble makes good sense. If you
migrate your SQL statement to an SP, you'll realize some performance gains,
clean up your code, and put the heavy lifting where it belongs.

Given your example, (and using MS SQL), you could create an SP:

CREATE PROCEDURE gensp_GetUsers
@p_vusername varchar(64),
@p_vzipcode varchar(12),
@p_vcity varchar(64)

AS
declare @l_vSQL varchar(2048) -- adjust for size up to 8000 if necessary
set @l_vSQL = 'SELECT name, address, zipcode, city, state,etc,etc,etc
FROM users
WHERE 0=0 '
IF @p_vusername <> ''
set @l_vSQL = @l_vSQL + 'AND [name] IN ('''+@l_vusername+''')'
IF @p_vzipcode <> ''
set @l_vSQL = @l_vSQL + 'AND zipcode IN ('''+@l_vzipcode+''')'
IF @p_vcity <> ''
set @l_vSQL = @l_vSQL + 'AND city IN ('''+@l_vzipcode+''')'
--etcetcetc
exec(@l_vSQL)

and call it with CFSTOREDPROC.

SQL caches the plan for the SP execution, so after the first run, the
performance is markedly superior to issuing an ad hoc query.

HTH,

[philh]

OK, crow eaten here. Constructing dynamic SQL does nothing for performance
gain (as has been pointed out so pointedly by Joe Celso et al.) But you should
still come up with a parameterized SP and let the DB do the lifting.

Mea culpa, mea culpa,....