build dynamic sql statement with table variable

I am trying to build a dynamic sql statement using a table variable. I am able to build the SQL statement and print it.

USE master
GO

DECLARE @loginarg nchar(128)
SET     @loginarg='DOMAIN\login'

DECLARE @RollbackSession table(
spid	        smallint,
status             nchar(50),
login		nchar(128),
hostname	        nchar(128),

blkby		char(5),
dbname		nchar(128),
command	        nchar(16),

cputime		integer,
diskio		integer,
lastbatch	        nchar(50),
programname        nchar(50),

spid2		smallint,
requestid	        integer
)

INSERT INTO @RollbackSession(spid, status, login, hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2, requestid)
EXEC sp_who2;

----this works.....
----SELECT * FROM @RollbackSession WHERE login=@loginarg;

DECLARE @sql nvarchar(4000)
SET @sql='SELECT * ' +
			 'FROM @RollbackSession WHERE Login LIKE ' +@loginarg

----this works.....
--PRINT @sql;

----this doesn't.....
EXECUTE sp_executesql @sql;

Open in new window

But then when running the EXECUTE statement, I get this error:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@RollbackSession".

I read that 'the batch that contains sp_executesql or the EXECUTE statement does not have access to variables or local cursors defined in the executed string' on BOL at https://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx; not sure if that's related.

I need the LIKE operator because I am adding wildcards once I can get this working.

Thanks!

pae2
pae2Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
You need to declare the table variable inside the dynamic SQL. Please try...

Please use this -

USE master
GO

DECLARE @loginarg VARCHAR(128)
SET @loginarg='DOMAIN\login'

DECLARE @sql VARCHAR(MAX) = ''
SET @sql='

DECLARE @RollbackSession table(
spid	        smallint,
status             nchar(50),
login		nchar(128),
hostname	        nchar(128),

blkby		char(5),
dbname		nchar(128),
command	        nchar(16),

cputime		integer,
diskio		integer,
lastbatch	        nchar(50),
programname          nchar(50),

spid2		smallint,
requestid	integer
)

DECLARE @loginarg nchar(128)
SET     @loginarg=''DOMAIN\login''

INSERT INTO @RollbackSession(spid, status, login, hostname, blkby, dbname, command, cputime, diskio, lastbatch, programname, spid2, requestid)
EXEC sp_who2;

SELECT * FROM @RollbackSession WHERE Login LIKE ' + '''' + '%' + @loginarg + '%' + '''' 

EXEC(@SQL)

Open in new window

0
 
pae2Author Commented:
Perfect - thank you Sir!
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.

All Courses

From novice to tech pro — start learning today.