0

I am currently trying to DECLARE a variable, @MyTable, as a user defined table type (udtt). However, which udtt it is declared as depends on the value of the variable @Schema. To do this, I have made the following attempts:

Attempt One:

IF (@Schema = 'sch_A') DECLARE @MyTable [sch_A].[udtt_Table]
IF (@Schema = 'sch_B') DECLARE @MyTable [sch_B].[udtt_Table]
IF (@Schema = 'sch_C') DECLARE @MyTable [sch_C].[udtt_Table]

Attempt Two:

DECLARE @sql = 'DECLARE @MyTable [' + @Schema + '].[udtt_Table]'

The problem with my fisrt attempt is that the variable @MyTable is declared by every IF statement, even when evaluated as false. This results in an error, stating that the variable @MyTable has already been declared, being thrown. This issue is discussed here.

The problem with my second is also to do with the scope of the variable @MyTable - although my understanding of why this is the case is more limited.

Can anyone suggest a way in which the desired result may be achieved?

J. Chapman
  • 305
  • 1
  • 3
  • 9
  • Basic rule: One variable, one type. You could use dynamic SQL to construct a miniature universe within which the variable has a type specified from the world outside, but that may be a long trip down a rabbit hole depending on what you plan to do after the declaration. – HABO Sep 12 '18 at 13:17
  • 1
    Please describe some more details about your final goal. What are you trying to achieve? This sounds [like an XY-Problem](https://en.wikipedia.org/wiki/XY_problem)... In Short: There is dynamic SQL, which might turn out as quite some pain in the neck... – Shnugo Sep 12 '18 at 13:17
  • Unless you use dynamic SQL, that's simply impossible. declare is a compile time action, conditions are run time actions. compile happens before running, so that's simply impossible. – Zohar Peled Sep 12 '18 at 13:33

0 Answers0