As indicated previously, I have been doing a lot more database based stuff in recent months and hence I have learned a number of new things. In the following I’ll be attempting to share my newly found knowledge on stored procedures and SQL case statements.

Stored procedures

Let’s start off by looking at some code:

CREATE PROCEDURE [dbo].[qGetBannerAd] (
@adBannerType int = NULL,
@ad_amsSectionID varchar(30) = NULL,
@adPageLocation int = NULL,
@NumOf int = 1
)
AS

DECLARE
@BaseSQL varchar(1000),
@SQLStatement varchar(8000)

/*
initialise the base SQL
if banner type is valueCLick get code
else get display name, filename, width and height
*/
IF @adBannerType = 0
SET @BaseSQL = ‘SELECT TOP ’ + cast(@NumOf as varchar(100)) + ‘AD.adAdvertsID, AD.adCodeSnippet, AD.adRanking’
ELSE
SET @BaseSQL = ‘SELECT TOP ’ + cast(@NumOf as varchar(100)) + ‘AD.adAdvertsID, AD.adDisplayName, AD.adBannerType, AD.adBannerLink, AD.adMaximumDisplays, AD.adRanking, AD.adHeight, AD.adWidth, AD.adFileName’

SET @BaseSQL = @BaseSQL + ’
FROM tbl_Adverts_Details AD
WHERE getDate() BETWEEN AD.adStartDateTime AND AD.adEndDateTime
AND AD.adPublish = 1
AND AD.adIsDeleted = 0’

/* if an internal make sure it’s not passed it’s max display span */
IF @adBannerType = 1
SET @BaseSQL = @BaseSQL + ’ AND AD.adTimesDisplayed < CASE AD.adMaximumDisplays WHEN - 1 THEN 200000000000000 ELSE AD.adMaximumDisplays END'

SET @SQLStatement = ’ AND AD.adBannerType = ’ + cast(@adBannerType as varchar(100)) + ’ AND AD.ad_amsSectionID = ’ + cast(@ad_amsSectionID as varchar(100)) + ’ AND AD.adPageLocation = ’ + cast(@adPageLocation as varchar(100))

/* Append the order by clause */
SET @SQLStatement = @BaseSQL + @SQLStatement + ’ ORDER BY NewID()’

/* Exceute the SQL */
EXEC(@SQLStatement)
GO

This stored procedure was created to retrieve a set of Banner ads for an application we had developed. In the first instance we create the stored procedure (SP) and declare the parameters that it expects to take (these are prefixed by the’@’ symbol). Next I declared a set of variables that I as to use to build up the SQL to be executed. As you can see after the declaration based on one of the parameters passed (@adBannerType) I determine which select statement to run and set it to @BaseSQL which I declared previously.

The rest of the SP follows the same kind of tack, only know I use the parameters passed to determine what kind of where clauses to run. Some things that may make you ponder at this stage are the CASE statement and ORDER BY NewID(), fear not I’ll be covering these shortly.

Now we have our SP how do we test it? Simply open your Query Analyzer and using the above example the statement to execute would be:

exec qGetBannerAd 1,2,2,3

the comma delimited values are the parameters that the SP expects and these are provided in the sequence they are specified. If you fail to do that the SP will more than likely fail.