Need help to upgrade a store proc. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help to upgrade a store proc.

I have this sp:
CREATE PROCEDURE sp_News_Get
@Sectionsvarchar(50),
@Languagebit,
@NbRow int
AS
EXEC (‘SELECT t.[ID], PublishDate, Title, tc.[Name], tc.NameFr FROM tblNews t, tblCategories tc WHERE t.CategoryType = tc.CategoryID AND t.CategoryType IN (‘
+ @Sections
+ ‘) AND t.[ID] IN
(SELECT TOP ‘ + @NbRow + ‘ x.ID FROM tblNews x
WHERE x.CategoryType = t.CategoryType AND x.Publish = 1 AND TypeId = 11 AND [Order] = 99 AND Language = ‘ + @Language + ‘
ORDER BY PublishDate DESC)’)
GO the results with the parameters ‘5,10’, 1, 3 is:
4632005-04-28 00:00:00.000Des bancs pour des élèves à MbandakaQuipsQuips
4442005-04-05 04:19:03.000Le contingent uruguayen de la MONUC quitte l#%92EquateurHumanitarianHumanitaire
4412005-03-31 07:38:47.000Des bancs pupitres fabriqués par des ex-combattantsQuipsQuips
4392005-03-29 08:13:18.000Janine, la détresse d#%92une déplacéeHumanitarianHumanitaire
4332005-03-21 01:45:40.000La MONUC sauve 1500 passagers de deux bateauxHumanitarianHumanitaire
4262005-03-16 00:00:00.000L#%92Hôpital de référence de Beni refait peau neuveQuipsQuips
The result is ordered by date but i would this sp to be stored by the category that have the most recent date and is ordered by date. The result must be: 4632005-04-28 00:00:00.000Des bancs pour des élèves à MbandakaQuipsQuips
4412005-03-31 07:38:47.000Des bancs pupitres fabriqués par des ex-combattantsQuipsQuips
4262005-03-16 00:00:00.000L#%92Hôpital de référence de Beni refait peau neuveQuipsQuips
4442005-04-05 04:19:03.000Le contingent uruguayen de la MONUC quitte l#%92EquateurHumanitarianHumanitaire
4392005-03-29 08:13:18.000Janine, la détresse d#%92une déplacéeHumanitarianHumanitaire
4332005-03-21 01:45:40.000La MONUC sauve 1500 passagers de deux bateauxHumanitarianHumanitaire Best regards Pascal
Add another clause to your ORDER BY statement: CREATE PROCEDURE sp_News_Get
@Sections varchar(50),
@Language bit,
@NbRow int
AS
EXEC (‘SELECT t.[ID], PublishDate, Title, tc.[Name], tc.NameFr FROM tblNews t, tblCategories tc WHERE t.CategoryType = tc.CategoryID AND t.CategoryType IN (‘
+ @Sections
+ ‘) AND t.[ID] IN
(SELECT TOP ‘ + @NbRow + ‘ x.ID FROM tblNews x
WHERE x.CategoryType = t.CategoryType AND x.Publish = 1 AND TypeId = 11 AND [Order] = 99 AND Language = ‘ + @Language + ‘
ORDER BY PublishDate DESC, t.CategoryType ASC)’)
GO
It’s not working, they still being on a bad order.
They must be grouped by category. Pascal
I am assuming what when you say "they must be grouped by category", you mean t.CategoryType. If you want the CategoryType FIRST, and then the Publishdate, then switch the order statement around: ORDER BY t.CategoryType ASC, PublishDate DESC Please post the results if you still have questions, because it is hard to know what your output will be if it still isn’t correct.
]]>