Table variables don't work with deletes ?

[Former Member]

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

[Former Member]

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.

[kevin_sherlock]

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

[Former Member]

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

[kevin_sherlock]

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....

[former_member188958]

I've opened CR 803575 for this issue.

-bret