TSQL - dynamic create and insert table

Question

I'm having problems dynamically creating a table and inserting it. I tried this in several ways and cannot achieve the desired result. The following is a simplified version of what I'm trying to do. Please note that this is only one attempt - I also tried others (for example, INSERT INTO [table] EXEC(@exe)).

Every time I try to do this, I get a message

Team completed successfully

but the table was not even 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)

- 1 . , ( ). , .

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

n , 1.

:

print @sqlText

, .

Answer 2

script, :

DECLARE @sqlText nvarchar

:

DECLARE @sqlText nvarchar(max)

Answer 3

: exec, :

EXEC ('IF OBJECT_ID (' '[BudgetProcedures].dbo.UnitOccupancy' ',' 'U' ') DROP [BudgetProcedures].dbo.UnitOccupancy;

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

INSERT INTO [BudgetProcedures]. [dbo]. [UnitOccupancy] (, , , ) (SELECT '' ExProp ''
"ExUnit" "" ExYrlen " "" ExLease "" ) ')