TSQL - Dynamic Create Table and Insert

Question

I am having a bit of trouble dynamically creating a table and inserting into it. I have tried this multiple ways and cannot produce a desirable outcome. See below for a simplified version of what I am trying to do. Note that this is only one attempt - I have tried others as well (such as INSERT INTO [table] EXEC(@exe)) with no success either.

Every time I attempt below, I get a message

Command(s) completed successfully

but no table has even been created.

I am using SQL Server 2008 R2.

DECLARE @sqlText nvarchar

SET @sqlText = 
N'
IF OBJECT_ID(''[BudgetProcedures].dbo.UnitOccupancy'', ''U'') IS NOT NULL
DROP TABLE [BudgetProcedures].dbo.UnitOccupancy;

CREATE TABLE [BudgetProcedures].dbo.UnitOccupancy (Property varchar(15)
                                                    ,Unit varchar(15)
                                                    ,YearLength varchar(15)
                                                    ,Lease varchar(15));

INSERT INTO [BudgetProcedures].[dbo].[UnitOccupancy] (Property, Unit, YearLength, Lease)

(SELECT ''ExProp''  
,''ExUnit''
,''ExYrlen''
,''ExLease''
)
'

EXEC(@sqlText)

Answer 1

You need to declare as:

DECLARE @sqlText nvarchar(1000)

Other way it has length 1 by default. And since you have new line in the beginning of statement it doesn't throw syntax exception(your query contains only new line symbol and it is legal). Delete new line and you will get an error.

https://msdn.microsoft.com/en-us/library/ms176089.aspx

When n is not specified in a data definition or variable declaration statement, the default length is 1.

You can use:

print @sqlText

to see what does your statement look like.

Answer 2

I was able to successfully test your script when I changed this:

DECLARE @sqlText nvarchar

To this:

DECLARE @sqlText nvarchar(max)