Generic T-SQL Stored Proc to generate RSS from any Table

This uses my UDF , "fnRFC822Date" from a previous post.

You simply supply a tablename, the names of the columns for the various RSS items
and channel items, a URL prefix for the link,
and the maximum number of items, and you get back well-formed (I hope) RSS!


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GenerateRSS1]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GenerateRSS1]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROC dbo.usp_GenerateRSS1
@RSSTableName varchar(50),
@ChannelTitle varchar(250),
@ChannelLink varchar(250),
@ChannelDescription varchar(500),
@ItemTitleColumnName varchar(50),
@ItemLinkColumnName varchar(50),
@ItemDescriptionColumnName varchar(50),
@ItemPubDateColumnName varchar(50),
@LinkUrlPrefix varchar(200),
@maxItems int
AS

DROP TABLE TEMPRSS -- ("DOH" FIX THIS LATER)

Declare @strSQL nvarchar(500)
set @strSQL ='select top ' +cast(@maxItems as varchar(3)) +' IDENTITY(int,1,1) AS ID , ['+
@ItemTitleColumnName +'] AS [TITLE], '+@ItemLinkColumnName +' AS LINK, ['+@ItemDescriptionColumnName +'] As [DESCRIPTION],'+
'dbo.fnRFC822Date(' +@ItemPubDateColumnName +') AS pubDate into TEMPRSS '+
' from ' + @RSSTableName + ' order by ' + @ItemPubDateColumnName + ' desc'
EXEC sp_ExecuteSql @strSQL
SELECt * from TEMPRSS
declare @results1 varchar(8000)
set @results1='<?xml version="1.0" ?><rss version="2.0"><channel><title>'
set @results1=@results1 +@ChannelTitle +'</title><link>'
set @results1=@results1+@ChannelLink +'</link><pubDate>'
set @results1=@results1 + dbo.fnRFC822Date(getUtcdate()) +'</pubDate><description>'
set @results1=@results1 +@Channeldescription +'</description><language>en-us</language>'
declare @max int
Select @max = max(ID)from TEMPRSS
declare @ctr int
set @ctr=1
declare @tit varchar(250)
declare @lnk varchar(250)
declare @desc varchar(8000)
declare @pubDt varchar(70)
WHILE (@ctr<=@max)
BEGIN
select @tit =[title] ,
@lnk=@LinkUrlPrefix +CAST(LINK AS VARCHAR(200)),
@desc=cast([description] as varchar(8000)),
@pubDt = pubDate
from TEMPRSS
where ID=@ctr
set @results1 =@results1 +'<item><title>'+@tit +'</title><link>' +@lnk
+'</link><description>'+@desc+'</description><pubDate>'+@pubDt+'</pubDate></item>'
set @ctr=@ctr+1
END
Set @results1=@results1+'</channel></rss>'

select @results1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SAMPLE EXEC:

DECLARE @RC int
DECLARE @RSSTableName varchar(50)
DECLARE @ChannelTitle varchar(250)
DECLARE @ChannelLink varchar(250)
DECLARE @ChannelDescription varchar(500)
DECLARE @ItemTitleColumnName varchar(50)
DECLARE @ItemLinkColumnName varchar(50)
DECLARE @ItemDescriptionColumnName varchar(50)
DECLARE @ItemPubDateColumnName varchar(50)
DECLARE @LinkUrlPrefix varchar(200)
DECLARE @maxItems int

Set @RSSTableName = 'RSS'
Set @ChannelTitle='this channel'
Set @ChannelLink = 'http://www.booboo.com'
SEt @ChannelDescription = 'the best channel ever'
SET @ItemTItleColumnName = 'Title'
Set @ItemLinkColumnName = 'Link'
Set @itemDescriptionColumnName ='description'
Set @itempubDateColumnName ='pubDate'
Set @linkUrlPrefix = 'http://www.booboo.com?'
Set @MaxItems =20
-- Set parameter values
EXEC @RC = [TEST].[dbo].[usp_GenerateRSS] @RSSTableName, @ChannelTitle, @ChannelLink,
@ChannelDescription, @ItemTitleColumnName, @ItemLinkColumnName, @ItemDescriptionColumnName,
@ItemPubDateColumnName, @LinkUrlPrefix, @maxItems

[Note to self:] Ahh, this still needs a little work, but I'm leaving it up in the meantime to see if anybody has feedback or ideas.