Sure.....
I have a web site where the user searches for courses in a database. They
can search on a criteria but can have multiple values this criteria. So, I
have an SP that excepts a parameter of type varchar. This parameter is a
comma delimited string that separates the values to search on. In my SP I
declare a variable called @sqlstring and then build it up like so:
DECLARE @sqlstring varchar(4000)
, @loop int
, @commapos int
, @len int
SELECT @loop = 1
SELECT @sqlstring = "SELECT * FROM <tablename> WHERE 0=1"
WHILE @loop <= <count of values in parameter string>
BEGIN
SELECT @commapos = CHARINDEX(",",@parameter)
IF @commapos<>0
BEGIN
SELECT @sqlstring = @sqlstring + "OR <field name> = '" +
LEFT(@parameter,@commapos - 1) + "'"
SELECT
END
ELSE
BEGIN
SELECT @sqlstring = @sqlstring + "OR <field name> = '" + @parameter
+ "'"
END
SELECT @len = LEN(@parameter)
SELECT @parameter = SUBSTRING(@parameter,@commapos + 1,@len - @commapos)
SELECT @loop = @loop + 1
END
Alternitively it may be easier for you to simply do:
SELECT @sqlstring = "SELECT * FROM <tablename> WHERE <fieldname> IN (" +
@parameter = ")"
Once you have built up your string you need to execute it by using:
EXEC(@sqlstring)
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
------------------------------------------------------------------
-----Original Message-----
From: Adrian Cesana [mailto:[EMAIL PROTECTED]]
Sent: 12 September 2000 19:53
To: [EMAIL PROTECTED]
Subject: RE: Stored Procedures
Could you provide an example of a dynamic stored procedure being executed
from CF.
Currently I just use lots of CFIF in my CFQUERY to build them dynamically,
how can I do this and pass it to a stored procedure. Obviously I dont have
experience with SP but would like to start utilizing them.
Thanks,Adrian
-----Original Message-----
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sebastian
Although it is a bit of a chore I'd use stored procedures all of the time if
you can. The reason for this is as follows:
When you use CFQUERY CF passes the SQL through to SQL Server which then
parses the SQL before executing it. If you use CFSTOREDPROC CF calls SQL
which SQL SERVER "knows about". i.e. it has already been parsed and it has
an execution plan for it. This is why you will see the improved performace
than Ben talks about in his book. There are also many other advantages (in
my view) in that getting SQL Server to handle the SQL makes it much more
flexible than building the SQL statement in CF. A classic example is if you
want to build a large dynamic SQL statement it is much easier to do it in an
SP by building up a string and then executing it than in CF (at least I
reckon it is).
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
------------------------------------------------------------------
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
