CODEWITHSUNDEEP
asp.netsql-serverunit-testing
Jacked_Nerd
Jacked_Nerd

Reputation: 237

Syntax for declaring a temp table within a SQL Server unit test

I am creating a SQL Server unit test for a stored procedure. I have most of it complete, but I am getting an error

Incorrect syntax near the word TABLE

from my auto-generated C# test method. I believe I am declaring my temp table wrong.

Here is the portion of my SQL code where I am declaring the temp table.

-- Database unit test for dbo.AutoExpireSchedule
DECLARE  @RC                        as INT,
         @Docid                     as VARCHAR(20),
         @NextExpTime               as DATETIME,
         @NextAssignTime            as DATETIME,
         @Appr                      as INT,
         @IsManualAssign            as BIT,
         @preparer                  as INT,
         @status                    as VARCHAR(5),
         @curTime                   as DATETIME,
         @Approut                   as INT,
         @date1                     as DATETIME,
         @date2                     as DATETIME,
         @nextExpDate               as DATETIME,
         @nextAssignDate            as DATETIME,
         @gap                       as INT,
         @RejectedCount             as INT,
         @TotalReqdAssnCnt          as INT,
         @folder                    as VARCHAR(20),
         @DfltAppr                  as INT,
         @AppInstID                 as INT,
         @tempTable                 as TABLE (id INT identity(1, 1), @DocId, @NextExpTime, @NextAssignTime, @Appr, @IsManualAssign),
         @NO_RESPONSE_FROM_PROVIDER as INT = 3;

I have also tried:

 @tempTable as TABLE (id INT identity(1, 1), DocId int, NextExpTime datetime, NextAssignTime datetime, Appr int, IsManualAssign bit),

@EDIT:

I realized there were some other syntax errors in here. So I have fixed those, however still getting same incorrect syntax near the word table. Here is the current code:

             @tempTable as TABLE (id INT identity(1, 1), DocId VARCHAR(20), NextExpTime DATETIME, NextAssignTime DATETIME, Appr INT, IsManualAssign BIT),

Upvotes: 1

Views: 283

Answers (2)

MK_
MK_

Reputation: 1169

You are indeed doing it wrong. What you're doing here is declaring a table variable. Your table variable should look like the following based on your code:

DECLARE @tempTable TABLE (
    id int IDENTITY(1, 1)
  , DocId varchar(20)
  , NextExpTime datetime
  , NextAssignTime datetime
  , Appr int
  , IsManualAssign bit
)

So... no @ sign in front of the columns in the table variable, throw out the AS keywords and all the types should be explicitly declared.

More on table variables can be seen for example on this link.

This might not be what you wish to achieve - if so, please add more information to your question.

Upvotes: 1

Thom A
Thom A

Reputation: 95567

You want:

DECLARE @TableVariable table(id INT identity(1,1),
                             DocId int,
                             NextExpTime datetime,
                             NextAssignTime datetime,
                             Appr int,
                             IsManualAssign bit);

No AS after the variable's name, and it is preceeded by DECLARE, like other variables.

Upvotes: 0

Related Questions