select * from @???

[capooti]

Hi, this could be very stupid for almost all of you but actually not for me, as I am still a beginner with Transact.
I want to write a stored procedure with several parameters. One of this parameters will be the name of a db's table. I can't manage doing it!
My code would be something like:
declare @tabxxx sysname
set @tabxxx = TABLE_NAMExxx
select * from @tabxxx

what is wrong with it?

best regards

 

[mfairchild]

Try this:

DECLARE @tabxxx sysname,
@SQL nvarchar(1000)

SET @tabxxx = 'TABLE_NAMExxx'

SET @SQL = "SELECT * FROM " + @tabxxx
EXEC(@SQL)

 

[capooti]

IT DOESN'T WORK, I HAVE TRIED IT ALREADY.

THANK YOU ANYWAY

 

[justinmarkham]

Hi there,
It will work but use single quotes:

DECLARE @tabxxx sysname,
@SQL nvarchar(1000)

SET @tabxxx = 'TABLE_NAMExxx'

SET @SQL = 'SELECT * FROM ' + @tabxxx
EXEC(@SQL)

Regards,

Justin

 

[ldandy]

I do not understand why you declare the table name in the procedure. If it comes as a paramter you don't declare it again. I have used the following procedure and it works.

CREATE PROCEDURE test_proc(@tabxxx sysname) AS
DECLARE @SQL nvarchar(1000)
SET @SQL = "SELECT * FROM " + @tabxxx
EXEC(@SQL)

 

[capooti]

thank you!
for ldandy: i made a sample without putting the code of the sp, of course in my sp the table name is a parameter

best regards