Skip to Content
0

Table variables don't work with deletes ?

Nov 16, 2016 at 10:07 AM

126

avatar image

Yet another problem table variables.

I'm using Sybase 16, SP02, PL04.

Try this...

12 rows in a @table, I delete 4 row and an extra 8 rows lost.

Is this feature reliable ?

declare @test table (id int not NULL, colid int not NULL)
insert into @test values ( 1, 1 )
insert into @test values ( 2, 1 )
insert into @test values ( 3, 1 )
insert into @test values ( 4, 1 )
insert into @test values ( 1, 2 )
insert into @test values ( 2, 2 )
insert into @test values ( 3, 2 )
insert into @test values ( 4, 2 )
insert into @test values ( 1, 3 )
insert into @test values ( 2, 3 )
insert into @test values ( 3, 3 )
insert into @test values ( 4, 3 )
insert into @test values ( 1, 4 )
insert into @test values ( 2, 4 )
insert into @test values ( 3, 4 )
insert into @test values ( 4, 4 )
select colid, count(1) from @test group by colid order by colid
delete @test where colid = 2
select "deleted ", @@rowcount
select colid, count(1) from @test group by colid order by colid
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Bret Halford
Nov 16, 2016 at 04:12 PM
0

I've opened CR 803575 for this issue.

-bret

Share
10 |10000 characters needed characters left characters exceeded
Kevin Sherlock Nov 16, 2016 at 09:27 PM
0

Table variables, and table based UDF's are a complete grease fire at this point. I would never recommend their use for anything other than non-production use

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Its completely unbelievable that these features are released without the most basic of tests being run first.

0

Agreed. My only guess is that it is because this feature was NOT borne from a specific SAP product requirement. I know from the many past Techwaves, ISUG conferences, etc that this feature has been begged for over and over. Many recent feature enhancements were driven by the need to integrate ASE as a backend to SAP applications, which is totally understandable. And therefore, were tested and vetted by real world usage. This one... not so much. Again, just my guess as I said, I have no facts to back that up, so this could just be my whining (again:) .

I had been working on a decent "string splitter" approach (via "tally" tables/functions, etc), but am constantly setback by such things as: "ASE has terminated this process", and having to reboot ASE because the descriptors stay in memory, and functions can't be dropped. ...blah blah....


0
Mike Willett Nov 18, 2016 at 09:09 AM
0

The last meeting I had with Sybase asking for string splitter, PIVOT table they said - if you want those features you shouldn't be using ASE.

Have you checked the ASE ideas page. They're actively discouraging use of ASE.

They added Global Temporary Tables in SP02 but there was a massive security hole (now fixed). Again - how do these things not get tested.

It feels like the users are the testers not SAP.

Share
10 |10000 characters needed characters left characters exceeded