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?
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot.