• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 708
  • Last Modified:

Error 2714 in SQL server

This is my simple procedure.....
Firstly I am just getting the value of table_name as the output and then when I try to run it 2nd time,
I get an error like this...
Server: Msg 2714, Level 16, State 6, Procedure insertReading, Line 5
There is already an object named 't' in the database.

----------
CREATE PROCEDURE insertReading  @channel INT AS

DECLARE @t VARCHAR(30)

select table_name INTO t from channel_table where id = @channel

select * from t
GO
-----------
Please Suggest.
Thanks,

Kanishk
0
kanishkpanwar
Asked:
kanishkpanwar
  • 3
  • 2
  • 2
  • +1
1 Solution
 
Anthony PerkinsCommented:
Try it this way:

CREATE PROCEDURE insertReading  @channel INT AS

DECLARE @t VARCHAR(30)

select @t = table_name from channel_table where id = @channel

select @t

GO
0
 
Anthony PerkinsCommented:
The reason you are getting the error "There is already an object named 't' in the database." is because the first time around you actually created a table call t.  Which on second thoughts that may be what you wanted.

Can you tell us what you are really trying to achieve?
0
 
HuyBDCommented:
Try delete t first

CREATE PROCEDURE insertReading  @channel INT AS

DECLARE @t VARCHAR(30)
drop table t if exists
select table_name INTO t from channel_table where id = @channel
select * from t
GO
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kanishkpanwarAuthor Commented:
@acperkins:-

My parameter is a number. I use it to find the table in which the values are inserted.
channel_table is like this..
id                table_name
0                 t_1
1                 t_2

based on user input the appropriate table is selected and values are inserted into the table.

I tried running your sample procedure but it still gives table_name as the out put and does not show anything from "select * from t"
0
 
kanishkpanwarAuthor Commented:
@HuyBD-

I got Syntax error in your stored procedure near select statement. :S
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CREATE PROCEDURE insertReading  @channel INT AS

DECLARE @t VARCHAR(30)

select @t = table_name from channel_table where id = @channel

EXEC ('SELECT * FROM '+@t)

GO
0
 
HuyBDCommented:
Try this

CREATE PROCEDURE insertReading  @channel INT AS

DECLARE @t VARCHAR(30)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[t]
select  table_name INTO t from channel_table where id = @channel
select * from t
GO
0
 
kanishkpanwarAuthor Commented:
@aneeshattingal :- your solution worked. thnx a ton.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now