Try building a string containing the SQL and dynamically build the last bit
only using IF ELSE or even Case statements (test to see which is quicker)
and then and then Execute it like so:
SELECT @sqlstring = 'SELECT programid, programname, meetingcode, startdate,
enddate, status, client_mst.clientname, (SELECT COUNT(regid) FROM
registration_mst, audience_mst WHERE registration_mst.audid =
audience_mst.audid AND audience_mst.programid = program_mst.programid) AS
regtotal FROM program_mst, client_mst WHERE program_mst.accountid =
@account AND program_mst.clientid = client_mst.clientid'
SELECT @sqlstring =
CASE @sort WHEN <value1> THEN @sqlstring + ' ORDER BY <value1>'
WHEN <value2> THEN @sqlstring + ' ORDER BY <value2>'
WHEN <value3> THEN @sqlstring + ' ORDER BY <value3>'
..
..
.. ELSE ...
END
EXEC (@sqlstring)
------------------------------------------------------------------
Andrew Ewings
Project Manager
Thoughtbubble Ltd
http://www.thoughtbubble.net
------------------------------------------------------------------
United Kingdom
http://www.thoughtbubble.co.uk/
Tel: +44 (0) 20 7387 8890
------------------------------------------------------------------
New Zealand
http://www.thoughtbubble.co.nz/
Tel: +64 (0) 9 419 4235
------------------------------------------------------------------
The information in this email and in any attachments is confidential and
intended solely for the attention and use of the named addressee(s). Any
views or opinions presented are solely those of the author and do not
necessarily represent those of Thoughtbubble. This information may be
subject to legal, professional or other privilege and further distribution
of it is strictly prohibited without our authority. If you are not the
intended recipient, you are not authorised to disclose, copy, distribute, or
retain this message. Please notify us on +44 (0)207 387 8890.
-----Original Message-----
From: Mark Johnson [mailto:[EMAIL PROTECTED]]
Sent: 10 November 2000 08:32
To: CF-Talk
Subject: Stored Procedures?
This one has stumped me for a while here so I thought I would through it out
to the rest of you. I have written a stored procedure for MS SQL 7.0 The
query is pretty basic it just selects some records from a table with a
subquery contained within the select that is performing a COUNT agregate.
By itself the stored proc works fine. However I need to be able to pass a
CFPROCPARAM to the stored proc that dynamically changes the SORT BY value.
So I tried to do that as follows:
CREATE PROCEDURE dbo.sp_foo @account int, @sort varchar(50)
AS
SELECT programid, programname, meetingcode, startdate, enddate, status,
client_mst.clientname,
(SELECT COUNT(regid) FROM registration_mst, audience_mst
WHERE registration_mst.audid = audience_mst.audid AND
audience_mst.programid = program_mst.programid) AS regtotal
FROM program_mst, client_mst
WHERE program_mst.accountid = @account AND program_mst.clientid =
client_mst.clientid
ORDER BY @sort
The problem is that apparently you can't use a column name as a variable
value. SQL 7 throws and error when I try to save the sp. So what I had to
do was to build out IF ELSE statements in the stored proc that check for the
value of @sort and change the entire query block.
Adding the IF ELSE statements significantly slowed down the speed of the
query. IF ELSE in transact SQL is extremely archaic and you have to nest
the IF ELSE statements to create an IF ELSEIF type statement. Anyway, is
there a way to do what I am trying to acomplish above? or am I stuck with
the IF ELSE nesting?
Mark Johnson
-----------------------
Senior Cold Fusion Developer
Cardinal Communications
----------------------------------------------------------------------------
--------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a
message with 'unsubscribe' in the body to [EMAIL PROTECTED]
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
