Stored procedure is not accepting the parameter

Question

Hello,

           I'm using SQL 2000 and create new simple stored procedure. And it's not accepting the parameter value in select * into. below is the stored procedure.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_test]
GO
create proc sp_test
( @tablename varchar(50)
 )
as
select * into @tablename from TableFromDatabase --where flag = @tablename
go
sp_test 'test'
go
select * from test

--------

I'm getting the error below

Server: Msg 170, Level 15, State 1, Procedure sp_test, Line 5
Line 5: Incorrect syntax near '@tablename'.

Any one having the solution on this.

Thanks

Mahes

Answer 1

So what do you want to do in the first place? That is, why do you want to provide a table name at run time? Why not simply use a temp table?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hello Erland,

           Thanks for your reply. My need is totally different. For understanding purpose, I created very simple stroed procedure. Please explain, what is the reason, I'm getting the error at the parameter.

Thanks

Mahes

---

Maheswaran Jayaraman

Answer 3

Hi,

Sure you have the error...

You can't do that. The name of the table can't be a variable. If you need to make it dinamic, you must create the insert in a varchar variable and run it using EXEC (@variable);

Take care with SQL injection.

Hope it helps,

Best regards,

Pedro Martins

Answer 4

Hello Pedro,

          Thanks for your reply. I'm using this code in my laptop. I'll NOT use any network. SO I don't need to worry about the SQL injection.

        I use to pass the table name in variable and stored it database. Below is my another version of stored procedure.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_test]
GO
create proc sp_test
(
 @tablename varchar(50)
 )
as
declare @sql varchar(8000)
set @sql = ''
set @sql = 'select * into '+ @tablename +' from TableFromDatabase '
print @sql
exec @sql
go
exec sp_test 'test'
go
select * from test

---

still I'm getting the error. can you please help me.

---

Maheswaran Jayaraman

Answer 5

Thanks everyone. I got the solution. I was missing () at the end.... below is the correct one.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[test]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_test]
GO
create proc sp_test
(
 @tablename varchar(50)
 )
as

declare @sql varchar(8000)
set @sql = ''
set @sql = 'select * into '+ @tablename +' from ATI_MO_TicketExpireProcessFlags'
print @sql
exec (@sql)
go
exec sp_test 'test'
go
select * from test

---

Maheswaran Jayaraman

Answer 6

I have to teach SQL to people who are as confused as you are, so please tell me why you thought this would work?  What do you think a table is? Why did this make sense to you?  People do not make random errors; they usually make errors based on misconceptions. Why would you think this will work for a table of automobiles and a table of squids? 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

You cannot do this, because in a relational database a table is supposed to model a unique entity. So passing a table name as a parameter does not make sense. It would be as if you could pass a parameter to "new" in C# to specify at run-time which class to instantiate.

Whence my question. There is a considerable proability that you had already gone astray when you wrote your procedure, and that the solution to your real problem is actually different.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hello mahesh ,

                         you have to use the dynamic query to pass the table name as a variable 

                        please see the below eg

                    

create proc sp_test1  -- '#test'

( 
   @tablename varchar(50)
 )
as

 declare @sql varchar(50)

 set @sql = 'select  * into  '+@tablename+'  from [HR].[Employees]'

 print @sql

 execute (@sql)

try and let us know if you have any query.

Answer 9

let's try to read the discussion

http://stackoverflow.com/questions/11359003/sql-server-stored-procedure-parameter-assignment