T.Ashish (8/12/2013)
declare @image1imagedeclare @text1text
declare @ntext1ntext
declare @varchar2varchar
declare @nvarchar2nvarchar
declare @varbinary2varbinary
declare @varchar1varchar(max)
declare @nvarchar1nvarchar(max)
declare @varbinary1varbinary(max)
First three variable declarations are definitely going to produce an error.
Yes of course they will. But this won't:
declare @image1varbinary(max)
declare @text1varchar(max)
declare @ntext1nvarchar(max)
declare @varchar2varchar
declare @nvarchar2nvarchar
declare @varbinary2varbinary
declare @varchar1varchar(max)
declare @nvarchar1nvarchar(max)
declare @varbinary1varbinary(max)
"Text, Ntext, and Image data types are invalid for local variables." is misleading:
DROP TABLE #TEST
CREATE TABLE #TEST (text1 text)
INSERT INTO #TEST (text1)
SELECT CAST(REPLICATE('A',8000) AS VARCHAR(MAX))
+ CAST(REPLICATE('B',8000) AS VARCHAR(MAX))
+ 'THE END'
SELECT SUBSTRING(CAST(text1 AS VARCHAR(MAX)),16001,7) FROM #TEST
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden