Table Data Type and MS SQL 7.0

[Mark Gambo]

Thanks to Szalmmany and Techgnome I was cruising along with SPROCS on my MS SQL2005 DB but when I went to work today tragedy struck. The SPROCS that I spend a weekend creating will no t run on our MS SQL 7.0 box specifically 7.0 doesn't like this line:

DECLARE @ListTbl TABLE(InvUnit varchar(100)

I am getting the following error:

Incorrect syntax near the Keyword 'Table'

Here is a copy of the SPROC:

USE IADATA
IF EXISTS (select * from syscomments where id = object_id ('TestSP'))
    DROP PROCEDURE TestSP
 
GO
CREATE PROCEDURE TestSP
    /*Declare Variables*/
    @ListStr varchar(100) /*Hold Delimited String*/
AS
 
DECLARE @ListTbl Table (InvUnit varchar(50)) /*Creates Temp Table*/
DECLARE @CP int /*Len of String */
DECLARE @SV varchar(50) /*Holds Result */
 
While @ListStr<>''
Begin
    Set @CP=CharIndex(',',@ListStr) /*Sets length of words - Instr */
    If @CP<>0
    Begin
        Set @SV=Cast(Left(@ListStr,@CP-1) as varchar) /*Copies Portion of String*/
        Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP) /*Sets up next portion of string*/
    End
    Else
    Begin
        Set @SV=Cast(@ListStr as varchar)
        Set @ListStr=''
    End
    Insert into @ListTbl Values (@SV) /*Inserts variable into Temp Table*/
End
 
Select InvUnit From @ListTbl LT
INNER Join dbo.Incidents ST on ST.Inv_Unit=LT.InvUnit

Here is a link to the post

BTW, this SP runs fne on a MS SQL2005 box. Any Ideas?

[brucevde]

Table does not exist in SQL7, it was a new feature in SQL2K.

Create Table #ListTbl (InvUnit varchar(50)) would be the equivalent.

You should also include a Drop Table #ListTbl at the end of the sproc.

Something as simple as this might work as well

Select * From Incidents Where CharIndex(Inv_Unit,@ListStr) > 0

[szlamany]

Wow - supporting SQL 7.0 through SQL 2005 is quite a requirement...

[Mark Gambo]

Wow - supporting SQL 7.0 through SQL 2005 is quite a requirement...

No not exactly, at work we have 7.0 but at home I have MSSQL 2000, MSSQL2K5 and MySQL 4.1. My job doesn't want to upgrade. Anyway I was aboiut to mark this tread Resolved anyway because I added another Variable and then masaged the input so that I got the desired results. Would it be better to do it the way you indicated or doing with variables?

Thanks

BTW when I get home I will post my solution.

[szlamany]

Charindex will disallow index use in the QUERY - but will work efficiently for small recordsets.

Building the table variable or temp table allows true "set-based" processing to handle the JOIN - so that indexes and proper query execution plans can be built.

It's your call in the long run - I use both the CHARINDEX and the TABLE/INSERT/JOIN tricks myself.