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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pae2Author Commented:
Perfect - thank you Sir!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.