Stored procedure problem

[stx]

Hi

This is my stored procedure...

create procedure get_tableInfo
(
@tablename char(8)
)

as
select * from @tablename
return


This doesn't work.
What i want to do is returning a selection on a table in my database. The name of the table is given as a parameter.
Any thoughts?

thnx

 

[Craig0201]

Ask in the SQL Server forum.

Craig

 

[RiverGuy]

It's not legal to do that. The closest thing you can do is dynamic sql:

create procedure get_tableInfo
(
@tablename char(8)
)

as
EXEC 'select * from ' + @tablename
return

 

[sunaj]

Hi,

I don't think Riverguy's suggestion will work (I couldn't get tit working at least) - but modified as below it will work.
Use the sp_executesql stored procedure to execute a sql statement in a string. You cannot concatenate in the call itself.
-----------------------------
create procedure get_tableInfo(@tablename char(8))
as
Declare @s nvarchar(255)
Set @s='select * from ' + @tablename
exec sp_executesql @s
return
-----------------------------



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[RiverGuy]

Good catch. Long day yesterday.

 

[Kris11]

Just a suggestion,
I think sp_executesql is one way of doing it but what RiverGuy missed out was just the brackets.

Following should also work

create procedure get_tableInfo
(
@tablename char(8)
)

as
EXEC ( 'select * from ' + @tablename )
return

We need paranthesis after EXEC.

-Kris

 

[RiverGuy]

Kris,
I think you right. I didn't check my notes on how I've used it in production. I think when I have used it, I assigned made a string such as:

SET @string = 'blah blah blah ' + @tableVariable
EXEC @string

Can't remember though. stx, just read up some on dynamic sql in BOL.

 

[stx]

thnx guys

you were great !!!