Need help to upgrade a store proc.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help to upgrade a store proc.

Author

Topic

pharvey
Starting Member

20 Posts

Posted - 2006-03-02 : 10:17:51

I have this sp:
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)')
GO

the results with the parameters '5,10', 1, 3 is:
463 2005-04-28 00:00:00.000 Des bancs pour des élèves à Mbandaka Quips Quips
444 2005-04-05 04:19:03.000 Le contingent uruguayen de la MONUC quitte l’Equateur Humanitarian Humanitaire
441 2005-03-31 07:38:47.000 Des bancs pupitres fabriqués par des ex-combattants Quips Quips
439 2005-03-29 08:13:18.000 Janine, la détresse d’une déplacée Humanitarian Humanitaire
433 2005-03-21 01:45:40.000 La MONUC sauve 1500 passagers de deux bateaux Humanitarian Humanitaire
426 2005-03-16 00:00:00.000 L’Hôpital de référence de Beni refait peau neuve Quips Quips

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:

463 2005-04-28 00:00:00.000 Des bancs pour des élèves à Mbandaka Quips Quips
441 2005-03-31 07:38:47.000 Des bancs pupitres fabriqués par des ex-combattants Quips Quips
426 2005-03-16 00:00:00.000 L’Hôpital de référence de Beni refait peau neuve Quips Quips
444 2005-04-05 04:19:03.000 Le contingent uruguayen de la MONUC quitte l’Equateur Humanitarian Humanitaire
439 2005-03-29 08:13:18.000 Janine, la détresse d’une déplacée Humanitarian Humanitaire
433 2005-03-21 01:45:40.000 La MONUC sauve 1500 passagers de deux bateaux Humanitarian Humanitaire

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-03 : 03:04:07

" i would this sp to be stored by the category that have the most recent date and is ordered by date"
Is this CategoryID or CategoryType ?

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources