2

I've declared a variable in a stored procedure:

DECLARE @CurrentChunk NVARCHAR(250)

I would like to use the length of the variable, i.e. 250, later in my sp for computational purposes, and I want to keep my code as dry as possible.

Here's my code (assume @Narrative is a param to the SP):

DECLARE @ChunkSizeCharacters INT, @NumChunks INT, @LoopIndex INT, @CurrentChunk NVARCHAR(250) SET @ChunkSizeCharacters = 250 -- HERE'S WHERE I WANT THE LENGTH OF @CurrentChunk SET @NumChunks = CEILING((LEN(@Narrative) * 1.0)/@ChunkSizeCharacters) SET @LoopIndex = 0; WHILE (@LoopIndex < @NumChunks) BEGIN SET @CurrentChunk = SUBSTRING(@Narrative, ((@LoopIndex * @ChunkSizeCharacters) + 1), @ChunkSizeCharacters) INSERT INTO [dbo].[Chunks] ([Chunk]) VALUES (@CurrentChunk) SET @LoopIndex = @LoopIndex + 1 END

Is there a way to ascertain the length of an NVARCHAR or VARCHAR variable definition (please read carefully -- I'm not looking for LEN())?

Improve this question
3
  • I suspect with some explanation of what you are trying to do you get rid of the loop here and greatly simplify this. Commented Feb 24, 2015 at 16:38
  • The loop is there just to substantiate the need for the length of @CurrentChunk, i.e. to show that I've actually contrived a computation that uses that value. Commented Feb 24, 2015 at 16:43
  • uuuhhh ok. That didn't mean much of anything to me but whatever. I just don't see a need for a loop from what you posted. Seems like a tally table could do this in a single pass. Commented Feb 24, 2015 at 16:48

3 Answers 3

Reset to default
4

It seems the MaxLength variant property returns the value you're looking for.

 DECLARE @Banana varchar(255) = 'This banana' SELECT SQL_VARIANT_PROPERTY(@Banana, 'MaxLength')

Returns 255.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Wow, that is sneaky. I love it!
A couple things here though -- in testing, it returns double the length for NVARCHAR since under the hood two bytes are used for each char. Also, if the variable is empty, it returns NULL, so to use this method, you have to put data in in first, even if it's an empty string (just like you have in your example with This banana).
Shame about the N mismatch -- although you can compensate for this by checking the BaseType property, that makes the expression unwieldy. But speaking of that, this actually allows you to determine the type of a T-SQL expression: SELECT SQL_VARIANT_PROPERTY(1.0 * 2, 'BaseType') yields numeric. Since the T-SQL rules for conversion are positively arcane, this can be a real time saver.
Yeah, this is definitely one for the personal tool kit. Thanks a lot.
I hadn't actually noticed that NVARCHAR is twice the length, but that makes sense. For the null issue, you could use SELECT SQL_VARIANT_PROPERTY(ISNULL(@Banana,''), 'MaxLength') instead, to avoid having to set it as an empty string.
|
2

If you don't mind overwriting the variable (and if you do, you can assign it to a temp NVARCHAR(MAX)):

SELECT @CurrentChunk = REPLICATE(0, 8000); SELECT @ChunkSizeCharacters = LEN(@CurrentChunk);

This trick does not and cannot work for NVARCHAR(MAX), but that's presumably no problem, given it's enormous maximum size.

Unfortunately T-SQL has nothing in the way of metadata properties for variables. Even determining the type of an expression is a chore.

Improve this answer

2 Comments

Unnecessarily clever, as it turns out. SQL_VARIANT_PROPERTY for the win!
Hmm...after testing, it's more of a toss-up between yours and SQL_VARIANT_PROPERTY. See my comments under that answer.
0

Interestingly, the value returned by that SELECT SQL_VARIANT_PROPERTY statement doesn't select into a plain, predefined variable. In the end, I used:

DECLARE @Text VARCHAR(400), @TextLen INT SELECT @TextLen = CAST(SQL_VARIANT_PROPERTY(ISNULL(@Text, ''), 'MaxLength') AS INT)

Works like a charm for me!

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.