CODEWITHSUNDEEP
sql-server
behzad razzaqi
behzad razzaqi

Reputation: 275

How can print table variable values in tsql?

I'm a new in sql server and write this tsql:

DECLARE @TempCustomer TABLE
(
   DATE_ nchar(20)
);

INSERT INTO 
    @TempCustomer 
SELECT distinct [ExecuteDate]
FROM 
     [ClubEatc].[dbo].[GetOnlineBills]

DECLARE  @intFlag int
set @intFlag=0;
WHILE (@intFlag <=2)
BEGIN
    PRINT @TempCustomer.DATE_
    SET @intFlag = @intFlag + 1
END
GO


but when i run that query i get this error:

Msg 137, Level 16, State 1, Line 30
Must declare the scalar variable "@TempCustomer".


How can i solve that problem?thanks all.

Upvotes: 5

Views: 15880

Answers (2)

gofr1
gofr1

Reputation: 15997

At first change table variable declaration:

DECLARE @TempCustomer TABLE (
    RN int IDENTITY(1,1) NOT NULL,
    DATE_ nchar(20)
);
DECLARE  @intFlag int

After INSERT use @@ROWCOUNT to obtain number of inserted rows:

INSERT INTO  @TempCustomer 
SELECT distinct [ExecuteDate]
FROM [GetOnlineBills]

SELECT @intFlag = @@ROWCOUNT

Then WHILE loop:

WHILE (@intFlag > 0)
BEGIN
    select DATE_
    from @TempCustomer
    WHERE RN = @intFlag

    SET @intFlag = @intFlag - 1
END

This solution is a little bit vague, but should work though.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

Not sure why you have such a requirement.. you can very well use select * from @tempCustomer as above said in comments.. But if you still want to loop thru and print individual dates the you can do some workaround

DECLARE @TempCustomer TABLE
(
   id int identity(1,1)
   DATE_ nchar(20)
);

INSERT INTO 
    @TempCustomer 
SELECT distinct [ExecuteDate]
FROM 
     [ClubEatc].[dbo].[GetOnlineBills]

DECLARE  @intFlag int
set @intFlag=0;
WHILE (@intFlag <=2)
BEGIN
    select * from @TempCustomer where id = @intFlag
    SET @intFlag = @intFlag + 1
END
GO

Upvotes: 4

Related Questions