August 29, 2003 at 4:08 pm
Let's say I have a stored procedure as follows:
CREATE PROCEDURE rotr_rptEmailCollectionReport
@startdate as datetime,
@enddate as datetime,
@sorttype as int
AS
select
name,
address,
city,
state,
zip,
ordercount
from historytable
where
entrydate between @startdate and @enddate
How can I utilize the @sorttype parameter to set the order by values differently?
Thus, if the @sorttype parameter is 0 the query should be:
select
name,
address,
city,
state,
zip,
ordercount
from historytable
where
entrydate between @startdate and @enddate
order by name
and if the @sorttype parameter value is 1 the query should be:
select
name,
address,
city,
state,
zip,
ordercount
from historytable
where
entrydate between @startdate and @enddate
order by ordercount
etc.
How do I accomplish this in my stored proc? Right now I have a bunch of statements like this:
if @sorttype = 0
<query with different order by listed here>
else if @sorttype = 1
<query with different order by listed here>
etc. and this is not going ot make it easy to change the stored proc later if necessary.
Ideas?
Thanks!
Mike
August 29, 2003 at 8:40 pm
Mike,
you're going to want to build and execute your sql statement dynamically.
Basically, you're going to build your SELECT statement as a text string. You will execute the SELECT statement using the sp_executesql stored procedure (You can find this sp within SQL Server Books Online).
Let me know if that answers your question.
August 30, 2003 at 5:10 am
Hey Mike,
Make it simple, as the other person said, use constructed queries, thats basically, u will have to arrange the SQL statement with a string variable,let say @STR, the have a string variable for SELECT in @str1, then FROM in @str2 and WHERE in @str3 and ORDER BY in @str4, So what u do for the ORDER BY is put a CASE statement and store all the combinations as a string against some values which u will be getting from the input parameter. Then concatenate these string variables in @STR and exec (@str). If you did not understand get back I shall shoot out a sample code. I have done it and it works great.
August 31, 2003 at 11:10 pm
Thanks for the advice. The only part I did not understand how to accomplish is using the CASE statement to create the ORDER BY clause. I'd love to see some sample code of that process.
Thanks again for all your help. This is an awesome place for SQL help! It is much appreciated. I've learned alot here already!
Mike
September 1, 2003 at 12:48 am
Hi there!
Here a sample code:
CREATE PROCEDURE test
@par tinyint
AS
select *
from TABLE
order by
case @par WHEN 1 THEN field1 ELSE NULL END,
case @par WHEN 2 THEN field2 ELSE NULL END
September 1, 2003 at 12:49 am
Here's an example of using a CASE statement in your ORDER BY clause. Although there're a lot of things going on in this example, hopefully, you can easily see the power of using CASE statements in clever locations and how to find ways to avoid using dynamically-generated SQL statements (even though the code looks lengthy and verbose).
//edit: I removed the complete sproc code for brevity. Contact me if you're curious.
...
ORDER BY
-- #### STRING COLUMNS #### --
CASE @sortColOrdinal
WHEN 0 THEN Topic_Name
WHEN 1 THEN (CASE b.Topic_Type WHEN 0 THEN 'Web Document' WHEN 1 THEN 'File' WHEN 2 THEN 'Web Link' ELSE '' END)
WHEN 2 THEN Type1_FileName
WHEN 5 THEN (SELECT COALESCE(Language_PrettyName,'') FROM app_languages WHERE ID = b.Language_ID)
WHEN 6 THEN (SELECT COALESCE(Status_Name,'') FROM Repository_Status WHERE ID = a.Status_ID)
WHEN 7 THEN (SELECT COALESCE(UserName,'') FROM User_Login WHERE ID = a.Lock_Owner_ID)
ELSE ''
END,
-- #### DATETIME COLUMNS #### --
CASE @sortColOrdinal
WHEN 3 THEN a.Date_Last_Modified
WHEN 4 THEN a.Date_Created
ELSE ''
END,
-- #### NUMERIC COLUMNS #### --
CASE @sortColOrdinal
WHEN 8 THEN (SELECT COUNT(x.ID) FROM Website_Element x INNER JOIN Website_Element_Data y ON y.Element_ID = x.ID INNER JOIN Website_Element_Promotion z ON z.Element_Data_ID = y.ID And Promotion_State_ID > 0 WHERE y.Repository_Topic_Details_ID = b.ID )
ELSE ''
END,
Topic_Name,
b.Date_Last_Modified
...
Edited by - kenwallacedesign on 09/03/2003 01:49:08 AM
September 2, 2003 at 11:01 pm
I've given this a try and here is the case statement I've come up with for the order by clause:
<procedure declaration and code here then the order by...>
order by
(
case @sorttype
when 0 then employees.fullname
when 1 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id)
when 2 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '')
when 3 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '')
when 4 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '')
when 5 then (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '')
else
''
end
)
set nocount off
GO
[/code]
The part where it says "when 2 then..." causes an error:
Syntax error converting the varchar value 'Poleski, Thomas' to a column of data type int.
What's up with that? How can I fix this problem?
Thanks!
Mike
September 2, 2003 at 11:24 pm
IN addition, the procedure I wrote has some columns defined as follows:
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id) as CustomersAsked,
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '') as CapturedEmails,
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '') as AskNextTime,
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '') as AskSixMonths,
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '') as NeverAskAgain
and I'd like to be able to ORDER BY EmailsCaptured or one of the other named columns, but when I try this in a case statement as follows:
case @sorttype
when 1 then EmailsCaptured
when 2 then NeverAskAgain
else
''
end
I get an error telling me that "EmailsCaptured" and "NeverAskAgain" are not valid column names. How can I accomplish THIS task?
Thanks!
Mike
September 3, 2003 at 12:56 am
Mike,
When a CASE statement is used in your ORDER BY, SQL Server will return errors if the columns referenced in your CASE statement are not all of similar data types.
If you notice in my example sproc, I placed three comma-separated CASE statements in a row in my ORDER BY clauses. This is a workaround to this issue.
quote:
ORDER BY
-- #### STRING COLUMNS #### --
CASE @sortColOrdinal
WHEN 0 THEN Topic_Name
WHEN 1 THEN (CASE b.Topic_Type WHEN 0 THEN 'Web Document' WHEN 1 THEN 'File' WHEN 2 THEN 'Web Link' ELSE '' END)
WHEN 2 THEN Type1_FileName
WHEN 5 THEN (SELECT COALESCE(Language_PrettyName,'') FROM app_languages WHERE ID = b.Language_ID)
WHEN 6 THEN (SELECT COALESCE(Status_Name,'') FROM Repository_Status WHERE ID = a.Status_ID)
WHEN 7 THEN (SELECT COALESCE(UserName,'') FROM User_Login WHERE ID = a.Lock_Owner_ID)
ELSE ''
END,
-- #### DATETIME COLUMNS #### --
CASE @sortColOrdinal
WHEN 3 THEN a.Date_Last_Modified
WHEN 4 THEN a.Date_Created
ELSE ''
END,
-- #### NUMERIC COLUMNS #### --
CASE @sortColOrdinal
WHEN 8 THEN (SELECT COUNT(x.[ID]) FROM Website_Element x INNER JOIN Website_Element_Data y ON y.Element_ID = x.[ID] INNER JOIN Website_Element_Promotion z ON z.Element_Data_ID = y.[ID] And Promotion_State_ID > 0 WHERE y.Repository_Topic_Details_ID = b.[ID] )
ELSE ''
END,
Topic_Name,
b.Date_Last_Modified
Edited by - kenwallacedesign on 09/03/2003 01:06:10 AM
September 3, 2003 at 1:19 am
Mike,
All arguments in the CASE statement used in your ORDER BY clause must be actual column names, not aliases.
In your situation, the following syntax should do the trick:
ORDER BY
-- #### STRING COLUMNS #### --
CASE @sorttype
WHEN 0 THEN employees.fullname
ELSE ''
END,
-- #### NUMERIC COLUMNS #### --
CASE @sorttype
WHEN 1 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id)
WHEN 2 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and isnull(email, '') <> '')
WHEN 3 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck <= @workingdate and isnull(email, '') = '')
WHEN 4 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck between DateAdd(d, 1, @workingdate) and DateAdd(d, 180, @workingdate) and isnull(email, '') = '')
WHEN 5 THEN (select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id and nextemailcheck = '12/31/2099' and isnull(email, '') = '')
ELSE ''
END,
employees.fullname
In the above code, I added a third ORDER BY clause. This way, in case the @sorttype resolves to one of the numeric columns, you are still doing a second sort based on alphabetical fullname. Take that out if you don't need your results formatted in this fashion.
I know it looks redundant to post each column data type in it's own CASE statement, but if you can visualize the end result of all the CASE statements combined it becomes more clear.
Imagine that @sorttype is 1, you are actually saying:
ORDER BY
'',
(select count(distinct id) from customers where id in (select customerid from orderheader where date_delivery between @startdate and @enddate and firstoperatorshiftid = employees.id) and emailcheckoperatorid = employees.id),
employees.fullname
.
Edited by - kenwallacedesign on 09/03/2003 01:29:36 AM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply