Why SQL server didn't see my Table definition?

  • Hello

    I declare a table as "@UserMidListTable" and I use it in inner join

    declare @string nvarchar(MAX)

    declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max) )

    SET @string='' SELECT TOP 10 @string = ISNULL('<tr> <td><a class="class_a" href="'+ISNULL(exhibitor.dbo.prpRead(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,48 ),'HTTP://namayeshgah.com') +'" target=_self> <div id="class_ans"> ' + ISNULL(exhibitor.dbo.prpRead(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1, 1338),'') +' </div> </a></td></tr>','') + @string

    FROM exhibitor.dbo.maintable INNER JOIN [@UserMidListTable] ON exhibitor.dbo.maintable.ID = [@UserMidListTable].[ValueMid]

    But I see this error

    Msg 208, Level 16, State 1, Line 3

    Invalid object name '@UserMidListTable'.

    Could you help me?

  • You have to alias table variables, and wrapping the table variable name in [] tells SQL that the table in question is a real table in the user database with that name, not a table variable. [] are used to allow otherwise illegal table names, such as ones starting with @ or #

    DECLARE @string NVARCHAR(MAX)

    DECLARE @UserMidListTable TABLE

    (

    Id INT ,

    Mid INT ,

    ValueMid INT ,

    CatParent INT ,

    [Enabled] INT ,

    LastUpdate DATETIME ,

    Company NVARCHAR(MAX)

    )

    SET @string = ''

    SELECT TOP 10

    @string = ISNULL('<tr> <td><a class="class_a" href="'

    + ISNULL(exhibitor.dbo.prpRead(exhibitor.dbo.maintable.Mid,

    exhibitor.dbo.maintable.ID,

    1, 48),

    'HTTP://namayeshgah.com')

    + '" target=_self> <div id="class_ans"> '

    + ISNULL(exhibitor.dbo.prpRead(exhibitor.dbo.maintable.Mid,

    exhibitor.dbo.maintable.ID,

    1, 1338), '')

    + ' </div> </a></td></tr>', '') + @string

    FROM exhibitor.dbo.maintable

    INNER JOIN @UserMidListTable mlt ON exhibitor.dbo.maintable.ID = mlt.[ValueMid]

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Hello GilaMonster

    thanks for your answer

    Right know I want to use my temp table in sp_executesql

    EXEC sp_executesql @SQLstring, N'@string nvarchar(max) output', @string=@ExeCommand output

    it is my code that generate my @SQLstring

    declare @string nvarchar(MAX)

    declare @UserMidListTable Table (Id Int,Mid int,ValueMid int,CatParent int, [Enabled] int,LastUpdate Datetime,Company nvarchar(max) )

    insert into @UserMidListTable values (1,20,100001,null,1,GETDATE(),null)

    insert into @UserMidListTable values (1,20,100007,null,1,GETDATE(),null)

    SELECT TOP 10 @string = ISNULL('<tr> <td><a class="class_a" href="'+

    ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,90 ),'HTTP://namayeshgah.com')

    +'" target=_self> <div id="class_ans"> ' + ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,150),'')

    +' </div> </a></td></tr>','') + @string COLLATE Latin1_General_CI_AS

    FROM exhibitor.dbo.maintable INNER JOIN @UserMidListTable UML ON exhibitor.dbo.maintable.ID = UML.[ValueMid]

    I see this error

    Must declare the table variable "@UserMidListTable".

    I read another post and forums, but I can't understand anything 🙁

  • Would you mind posting the code so that it's readable?

    DECLARE @string NVARCHAR(MAX)

    DECLARE @UserMidListTable TABLE

    (

    Id INT ,

    Mid INT ,

    ValueMid INT ,

    CatParent INT ,

    [Enabled] INT ,

    LastUpdate DATETIME ,

    Company NVARCHAR(MAX)

    )

    INSERT INTO @UserMidListTable

    VALUES ( 1, 20, 100001, NULL, 1, GETDATE(), NULL )

    INSERT INTO @UserMidListTable

    VALUES ( 1, 20, 100007, NULL, 1, GETDATE(), NULL )

    SELECT TOP 10

    @string = ISNULL('<tr> <td><a class="class_a" href="'

    + ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,

    exhibitor.dbo.maintable.ID,

    1, 90),

    'HTTP://namayeshgah.com')

    + '" target=_self> <div id="class_ans"> '

    + ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,

    exhibitor.dbo.maintable.ID,

    1, 150), '')

    + ' </div> </a></td></tr>', '') + @string COLLATE Latin1_General_CI_AS

    FROM exhibitor.dbo.maintable

    INNER JOIN @UserMidListTable UML ON exhibitor.dbo.maintable.ID = UML.[ValueMid]

    The code above (which is just a reformatted version of what you posted) will not throw that error. If you have code throwing errors, post the actual code that's throwing the error.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sorry because of my dirty code.

    Yes you are right, it work independently.

    but when I use it as "@SQLstring" in procedure "sp_executesql", I have this error "Must declare the table variable "@UserMidListTable".

    EXEC sp_executesql @SQLstring, N'@string nvarchar(max) output', @string=@ExeCommand output

    I think, because of using temp table "@userMidList" in procedure "sp_executesql" I have this error, Isn't it?

  • What is the exact value of @SQLString? It's not the string built up in previous statements as far as I can see because that contains no reference to the table variable.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SELECT TOP 10

    @string = ISNULL('<tr> <td><a class="class_a" href="'+

    ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,90 ),'HTTP://namayeshgah.com')

    +'" target=_self> <div id="class_ans"> ' +

    ISNULL(exhibitor.dbo.prpReadTypeID(exhibitor.dbo.maintable.Mid,exhibitor.dbo.maintable.ID,1,150),'')

    +' </div> </a></td></tr>','') +

    @string COLLATE Latin1_General_CI_AS

    FROM exhibitor.dbo.maintable

    INNER JOIN @UserMidListTable UML ON exhibitor.dbo.maintable.ID = UML.[ValueMid]

    it should be the variable @SQLstring that I use in Procedure "sp_executesql".

  • Then just add the declaration and inserts of the table variable into the dynamic string, so that it's the entire thing you posted originally.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (12/2/2013)


    You have to alias table variables, and wrapping the table variable name in [] tells SQL that the table in question is a real table in the user database with that name, not a table variable. [] are used to allow otherwise illegal table names, such as ones starting with @ or #

    Hi Gail,

    This is new to me. Can you tell me how do I ensure/check whether the table variable wrapped in [] is treated as a real table? Where/how to see the difference whith the @ and # prefixes for the temp tables?

    Regards,

    IgorMi

    Igor Micev,My blog: www.igormicev.com

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply