adrian@heywood.com.au (adrian) wrote in message news:<19ef5f9f.0307072230.2ee3c9e3@posting.google.com>...
> hi all
>
> this is my first post to this group, so pls bear with me while i try
> to make some sense.
>
> i am trying to create a sproc which uses dynamic sql to target a
> particuar table eg. '[dbo].[' + @tableID + '_articles']' and perform
> some actions.
>
> i am using @tableID [int] as a passes parameter for the sproc.
>
> everything seems to work fine until i try and manipulate a parameter
> which is of text data type.
>
> the error occurs when i try to build the dynamic sql string and append
> the text type variable.
>
> eg.
>
> CREATE PROCEDURE [procArticlesInsert]
> (
> @siteID [int],
> @strShortTitle [varchar](40),
> @strLongTitle [varchar](60),
> @strShortContent [text],
> @strLongContent [text],
> @intSectionID [int],
> @intTemplateID [int],
> @intStatusID [int]
> )
>
> AS
>
> DECLARE @strSQL varchar (1000)
> DECLARE @strSiteID varchar (10)
> SET @strSiteID = CAST(@siteID AS varchar)
>
> SET @strSQL = ('INSERT INTO [' + @strSiteID + '_articles] ' +
> ' ( [dateEntered], ' +
> ' [shortTitle], ' +
> ' [longTitle], ' +
> ' [shortContent], ' +
> ' [longContent], ' +
> ' [sectionID], ' +
> ' [templateID], ' +
> ' [statusID]) ' +
> 'VALUES ' +
> ' (' + CAST(GETDATE() AS VARCHAR) + ', ' +
> '''' + @strShortTitle + ''', ' +
> '''' + @strLongTitle + ''', ' +
> '''' @strShortContent , ' +
> ' @strLongContent , ' +
> CAST(@intSectionID AS VARCHAR) + ', ' +
> CAST(@intTemplateID AS VARCHAR) + ', ' +
> CAST(@intStatusID AS VARCHAR) + ')')
> GO
>
> i could cast the text fields (@strShortContent , @strLongContent) to
> varchar, but the restriction of 8000 characters will not go down so
> nicely.
>
> if anyone has any ideas or alternatives to what i am trying to
> achieve, i would love to hear from you.
>
> thanks