How can print table variable values in tsql?

Question

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.

@a_horse_with_no_name that show me all data! i want show record by record — behzad razzaqi
– behzad razzaqi, Commented Sep 15, 2016 at 8:57
in loop u r setting @intFlag how does it make difference ithink quetion is not clear — sandeep rawat
– sandeep rawat, Commented Sep 15, 2016 at 9:17

Kannan Kandasamy · Accepted Answer · 2016-09-15 09:17:14Z

4

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

answered Sep 15, 2016 at 9:17

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gofr1 · Accepted Answer · 2016-09-15 09:29:07Z

0

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.

edited Sep 15, 2016 at 9:29

answered Sep 15, 2016 at 9:18

gofr1

16k11 gold badges47 silver badges56 bronze badges

Collectives™ on Stack Overflow

How can print table variable values in tsql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related