none
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

    Friday, January 04, 2013 11:04 PM

Answers

  • 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, esquel@sommarskog.se
    Saturday, January 05, 2013 10:29 AM

All replies

  • 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, esquel@sommarskog.se
    Friday, January 04, 2013 11:12 PM
  • 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

    Saturday, January 05, 2013 12:13 AM
  • 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

    Saturday, January 05, 2013 12:17 AM
  • 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

    Saturday, January 05, 2013 12:39 AM
  • 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

    Saturday, January 05, 2013 12:48 AM
  • 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

    Saturday, January 05, 2013 1:52 AM
  • 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, esquel@sommarskog.se
    Saturday, January 05, 2013 10:29 AM
  • 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.

    Saturday, January 05, 2013 6:11 PM
  • let's try to read the discussion

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

    Saturday, January 05, 2013 6:40 PM