SQLTeam.com | Weblogs | Forums

User Define Table Type Syntax?


#1

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
AS
BEGIN
SET NOCOUNT ON;
declare @A table ( found varchar(100));
insert into @A(found)
SELECT item FROM @List -- into @A Table ?
END

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

END

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 !


#2

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