EXEC statement TSQL dynamic stored procedures

Hello.

I am trying to build a dynamic stored procedure using the EXEC statement.

I want to dynamically populate the SELECT TOP int syntax.

Here is my query:

USE [ALERTS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[select_features_byIsFeature_byWebsiteId]
(
@website_id int,
@items varchar (10)
)
AS

DECLARE @sql varchar(500)

IF @items > 0
BEGIN
      SET @sql = 'SELECT TOP ' + @items + ' c.content_linkText, c.content_pageHeader, c.content_abstract, ' +
            'c.content_hasImage, c.content_hasThumbnail, ' +
            'c.content_body, c.content_featureLinkText, c.content_featureHeader, ' +
            's.slug_text, ' +
            'i.image_filename, i.image_alt_attr, i.image_category_id, ' +
            'n.nav_homePageImageCatID, n.nav_useDates, n.nav_textAbstract, n.nav_numberOfCharsInAbstract ' +
'FROM  [content] c ' +
'INNER JOIN slugs s ON c.content_slug_id = s.slug_id ' +
'INNER JOIN navigation n ON c.content_nav_id = n.nav_id ' +
'LEFT JOIN images i ON c.content_thumbnail_id = i.image_id ' +
'WHERE content_client_id = @website_id AND content_showOnHomepage = 1 AND content_online = 1 ' +
'ORDER BY content_dateAdded DESC'
END

ELSE
BEGIN
      SET @sql = 'SELECT c.content_linkText, c.content_pageHeader, c.content_abstract, c.content_hasImage, c.content_hasThumbnail, ' +
            'c.content_hasImage, c.content_hasThumbnail, ' +
            'c.content_body, c.content_featureLinkText, c.content_featureHeader, ' +
            's.slug_text, ' +
            'i.image_filename, i.image_alt_attr, i.image_category_id, ' +
            'n.nav_homePageImageCatID, n.nav_useDates, n.nav_textAbstract, n.nav_numberOfCharsInAbstract ' +
'FROM  [content] c ' +
'INNER JOIN slugs s ON c.content_slug_id = s.slug_id ' +
'INNER JOIN navigation n ON c.content_nav_id = n.nav_id ' +
'LEFT JOIN images i ON c.content_thumbnail_id = i.image_id ' +
'WHERE content_client_id = @website_id AND content_showOnHomepage = 1 AND content_online = 1 ' +
'ORDER BY content_dateAdded DESC'
END

EXEC(@sql)




I can't get it to accept the @items paramter.

Any advice anyone?


Thanks,
Dave



Start Free Trial