December 2, 2013 at 1:27 am
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?
December 2, 2013 at 1:39 am
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
December 2, 2013 at 5:38 am
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 🙁
December 2, 2013 at 5:55 am
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
December 2, 2013 at 6:46 am
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?
December 2, 2013 at 6:51 am
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
December 2, 2013 at 6:57 am
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".
December 2, 2013 at 7:05 am
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
December 3, 2013 at 3:23 am
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