4

In SQL Server (2008 R2), instead of doing this:

DECLARE @testVar VARBINARY(64);

I would like to do this:

DECLARE @varSize INT;
SET @varSize = 64;
DECLARE @testVar VARBINARY(@varSize);

But I get this error:

Incorrect syntax near '@varSize'.

How can I do something like this or force SQL to evaluate @varSize?

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
pcantin
  • 554
  • 8
  • 17
  • 3
    Why? Just declare with max size (such as `8000` or `max`). Won't waste you any actual storage space. – GSerg Sep 14 '12 at 14:43
  • You can't force SQL to evaluate it because the processing is actually done in two stages: 1) compilation - all declarations are compiled and variables are allocated and defaults are set, it is done **ONCE** only per procedure/package. 2) run-time - the processing instructions are run, this happens every time your run the procedure/SQL. `DECLARE ...` is compilation time statement, `SET ...` is run-time statement. – Germann Arlington Sep 14 '12 at 14:55
  • 1
    NB: `MAX` does have lower performance than `8000`. [ref](http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/) – Martin Smith Sep 14 '12 at 14:56
  • @MartinSmith while I'm sure it applies to varbinary as well, the tests on that page (even though some *mention* varbinary) all handle varchar. Not trying to cast doubt, I just wonder if the delta is the same magnitude for varbinary (without stopping what I'm working on and testing, I'd suspect it to be less significant). – Aaron Bertrand Sep 14 '12 at 15:15

1 Answers1

8

For a variable, why don't you just use MAX?

DECLARE @testVar VARBINARY(MAX);

This isn't the 70s anymore. Your system can handle it. In fact if what you want to do were possible, I suspect you'd waste more resources doing that than you would just declaring the variable as MAX in the first place.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks. It's the over-optimization syndrome that hit me. And the fact that I don't know enough about modern DBs – pcantin Sep 14 '12 at 15:11