Prev: SQL Connection from VS 2008 Express to SQLExpress database
Next: Name of MS Online Classes
From: scott on 2 Jul 2010 12:01
How can I declare a NTEXT parameter within a sproc? The @newsstory parameter
is a ntext data type and I can't get the syntax right. The table column it
represents is ntext 8000 data type and being used for a fairly decent size
news article. If anyone has a better idea for what data type would be
better, I'd appreciate the advice.

The db is on SQL2K, but I'm going to move to SQL2K8 soon.


CODE
=======

@NewsSummary varchar (750) OUTPUT,

@NewsStory ntext OUTPUT,


From: John Bell on 2 Jul 2010 12:24
On Fri, 2 Jul 2010 11:01:04 -0500, "scott" <sbailey(a)mileslumber.com>
wrote:

>How can I declare a NTEXT parameter within a sproc? The @newsstory parameter
>is a ntext data type and I can't get the syntax right. The table column it
>represents is ntext 8000 data type and being used for a fairly decent size
>news article. If anyone has a better idea for what data type would be
>better, I'd appreciate the advice.
>
>The db is on SQL2K, but I'm going to move to SQL2K8 soon.
>
>
>CODE
>=======
>
>@NewsSummary varchar (750) OUTPUT,
>
>@NewsStory ntext OUTPUT,
>


You can't declare them as local variables and you will get the error
message

Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.

For nvarchar you are limited to 4000 characters so you would need to
chunk up the ntext value and process it separately if you want to do
something that does not take a ntext parameter etc.

For stored procedure parameters you can pass then e.g.

USE TEMPDB
GO
ALTER PROCEDURE spr_test (
@NewsSummary varchar (750) OUTPUT,
@NewsStory ntext OUTPUT )
AS
BEGIN
SET NOCOUNT ON ;
DECLARE @varcharvar nvarchar(4000) ;
SET @varcharvar = CAST(@NewsStory as nvarchar(4000) );
SELECT @newssummary, @varcharvar
END

But making it an output parameter doesn't make a great deal of sense
as you can't have ntext local variables.

John
From: Erland Sommarskog on 2 Jul 2010 18:22
scott (sbailey(a)mileslumber.com) writes:
> How can I declare a NTEXT parameter within a sproc?

You can't.

> The db is on SQL2K, but I'm going to move to SQL2K8 soon.

You will have to wait until then. In SQL 2008 there is the data type
nvarchar(MAX) which in in difference to ntext is a first-class citizen.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: SQL Connection from VS 2008 Express to SQLExpress database
Next: Name of MS Online Classes