January 15, 2008 at 12:57 am
Hi guys,
Basically, this is what I'm doing:
DECLARE @SearchedRecs TABLE (
nJobIDPK int,
nSupID int,
szPolicyName varchar(100),
szClaimNo varchar(100),
szPolicyNo varchar(100),
szAdjusterRefNo varchar(100),
szAddress varchar(100)
)
@SearchedRecs gets filled by another query and it's not empty.
set @sql = N'select nJobIDPK,nSupID,szPolicyName,szClaimNo,szPolicyNo,szAdjusterRefno from @SearchedRecs where some condition....'
select nJobIDPK,nSupID,szPolicyName,szClaimNo,szPolicyNo,szAdjusterRefno from @SearchedRecs where nJobIDPK >=0 and nSupID=88
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@SearchedRecs'.
SQL complains telling me the temp table need to be declared when it is. Lost.
Thx for your time and effort.
Cheers.
January 15, 2008 at 1:02 am
You've user a tablevariable. That is only available in your own session.
A global temptb (##tb) will fix this.
Keep in mind this affects multiple threads using the same temp object !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 15, 2008 at 1:21 am
You can use a normal temp table (#Tablename) as its visible in the procedure that created it, and any procedure or dynamic SQL called by that procedure.
A table variable has the same scoping rules as all other variables - only visible in the proc that declared it, not in any child procs or dynamic QSL
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
January 15, 2008 at 9:03 pm
Hi,
Thks for taking the time to look through this but I'm not in favour of using #temp tables due to performance reasons.
ALZDBA
- Yes, this whole SP is excuted in the same session.
Any more clues ? 😉
GilaMonster (1/15/2008)
You can use a normal temp table (#Tablename) as its visible in the procedure that created it, and any procedure or dynamic SQL called by that procedure.A table variable has the same scoping rules as all other variables - only visible in the proc that declared it, not in any child procs or dynamic QSL
January 15, 2008 at 9:44 pm
santosh (1/15/2008)
Hi,Thks for taking the time to look through this but I'm not in favour of using #temp tables due to performance reasons.
Really?
Can you share your negative experience?
Because I use # tables a lot and performance never suffered.
_____________
Code for TallyGenerator
January 15, 2008 at 11:53 pm
santosh (1/15/2008)
Hi,Thks for taking the time to look through this but I'm not in favour of using #temp tables due to performance reasons.
Yet you use table variables. Why's that?
The only major differences between temp tables and table variables is the method of declaring, the scoping and permissible indexes.
Both are created in TempDB, both are given entries in the tempdb system tables, bith can be persisted to disk if they grow large enough.
I'd actually rather use temp tables than table variables because of the limited indexing options available to table variables (and that they don't have statistics)
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply