If SQL variable exists

Question

I'm writing some code that uses a significant quantity of dynamic SQL and as a result, there are some instances when a third party application may or may not have a SQL variable declared.

Is there a way to test if a variable has been declared. Some psuedo code would be:

IF OBJECT_ID(N'@var' 
) IS NOT NULL
BEGIN
    DECLARE @var AS varchar(max)
END

Also, is there a way to list all of the variables currently declared like a local watch window?

Answer 1

No.

T-SQL declares variables per-batch - they do not have any scoping. You need to ensure all the variables that are actually used are declared from the outside - by the time the batch runs, it's too late.

It's not clear what you're trying to accomplish, and what kind of constraints you can impose on the code fragments. One alternative would be to use something other than variables - for example, a common table variable that would be used for all the other "pseudo-variables". Something like this:

declare @parameters table
(
  Id varchar(20),
  Value sql_variant
);

-- This is the code generated by the 3rd party; update might be better than insert
insert into @parameters values ('MyVar', 42);

-- Using the variable - you get NULL or the actual value
declare @MyVar int;
select @MyVar = cast(Value as int) from @parameters where Id = 'MyVar';

All the variables are then declared by your part of the code (or not at all), and the 3rd party only has an option to change them from their defaults. Of course, this may be entirely useless for your use-case - it's not clear what kind of scenarios you're actually expecting to occur.

In general, slapping together pieces of T-SQL is tricky. Since there's no scoping, there's no way of preventing one fragment from destroying the whole batch. If you can afford any checks at all, they need to be on a different layer - and you may have to change the names of the variables in the fragments to avoid collisions. Another option might be to prohibit the 3rd party from declaring any variables at all, and instead requiring them to register a variable from your side - that would allow you to choose names that avoid conflicts.