SQLTeam.com | Weblogs | Forums

User Define Table Type Syntax?

This is an extension of an earlier question Goal pass in an array, do an inner join against another list and thereby find the ones that match.

First theres a user Table Type ( dbo.,StriingList is User Defined Table varchar (100)

And it does load but then I try to compare it and I get error- Stored procedure looks like this

ALTER PROCEDURE [dbo].[EAGetSupportedNames]

@List AS dbo.StringList READONLY
declare @A table ( found varchar(100));
insert into @A(found)
SELECT item FROM @List -- into @A Table ?

SELECT found FROM [dbo].[enDeviceNames] inner join @A on @A.found =[dbo].[enDeviceNames].Name


When I execute to save I get this message
Must declare the scalar variable "@A"

Why scalar ? @A is already declared as a table

Thanks !

Dunno if you can use the Table Variable name "@A" as-is, so I suggest using an alias name - "A" would do!

SELECT found 
FROM [dbo].[enDeviceNames] 
    inner join @A AS MyAliasName
        on MyAliasName.found =[dbo].[enDeviceNames].Name

Personally I would use an Alias name for the table too - very "Bulky" to keep repeating the Schema / Table name as a prefix (and its optional anyway, unless the query is ambiguous - same column name in two different tables.

So this perhaps?

SELECT found 
FROM [dbo].[enDeviceNames]  AS DN
    inner join @A AS A
        on A.found = DN.Name