Link to home
Start Free TrialLog in
Avatar of caoimhincryan
caoimhincryan

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of caoimhincryan
caoimhincryan

ASKER

Thanks for quick reply. It seems to have done the job.

Thanks a lot.