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]
