• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 634
  • Last Modified:

Temporary table and Table variable issue

I am wondering why this temporary table approach works in my stored procedure and my table variable approach doesn't.

I get this error ONLY for the variable table approach for each line that contains the WITH(NOLOCK). i.e- Temporary approach works fine

"Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon."

Im using SQL Server 2005. Any ideas?
-- TEMPORARY APPROACH --
ALTER PROCEDURE [dbo].[GetWriteAccess1]
	@UserName nvarchar(256),
	@UserId uniqueidentifier
AS
BEGIN
	SET NOCOUNT ON;
 
		create table #custom_Users
		(
			UserName			nvarchar(256),
			UserId				uniqueidentifier
		)
 
		insert into #custom_Users (UserName, UserId) VALUES (@UserName, @UserId)
 
IF (@UserId IS NULL)
			RETURN(1)
 
	SELECT u.UserName, wa.WriteAccess, wa.Write_Id, wt.WriteName
	FROM #custom_Users u WITH(NOLOCK)
	JOIN tb_WriteAccess wa WITH(NOLOCK) ON u.UserId = wa.[User_Id]
	JOIN tb_WriteType wt WITH(NOLOCK) ON wa.Write_Id = wt.Write_Id
	WHERE  wa.[User_Id] = @UserId
 
	select * from #custom_Users
 
END
 
-- VARIABLE APPROACH --
declare @custom_Users table
		(
			UserName			nvarchar(256),
			UserId				uniqueidentifier
		)
 
		insert @custom_Users (UserName, UserId) values (@UserName,@UserId)
 
IF (@UserId IS NULL)
			RETURN(1)
 
	SELECT u.UserName, wa.WriteAccess, wa.Write_Id, wt.WriteName
	FROM @custom_Users u WITH(NOLOCK)
	JOIN tb_WriteAccess wa WITH(NOLOCK) ON u.UserId = wa.[User_Id]
	JOIN tb_WriteType wt WITH(NOLOCK) ON wa.Write_Id = wt.Write_Id
	WHERE  wa.[User_Id] = @UserId
 
	select * from @custom_Users
 
END

Open in new window

0
caoimhincryan
Asked:
caoimhincryan
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for temp tables or table variables, you don't need to use NOLOCK at all, as there cannot be any other process using that same table.
so, just remove NOLOCK from temp tables or table variables, to start with.
0
 
caoimhincryanAuthor Commented:
Thanks for quick reply. It seems to have done the job.

Thanks a lot.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now