Help!! Create Table from Variable !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help!! Create Table from Variable !!!

Author

Topic

cjsteury2
Starting Member

14 Posts

Posted - 2008-06-16 : 00:03:16

I am passing a variable to a stored procedure using
db.ExecuteNonQuery("dbo.CreateTable", @symbol); < C# CODE >
the variable shows up fine but the stored procedure does not create the table...
I have tried everything... here are two versions of code that do not work...

using Dynamic Sql....

CREATE PROCEDURE dbo.CreateTable
@symbol nvarchar(10)
AS
DECLARE @Sql varchar
SELECT @SQL = 'Create Table [dbo].['+ @symbol +'](Symbol float'
SELECT @SQL = @SQL + '
, [Date] datetime
, [Open] float
, High float
, [Low] float
, [Close] float
, Volume integer)'
EXEC (@SQL)

this does nothing

And this one...(the longer version)

CREATE PROCEDURE dbo.CreateTable
(
@Symbol as varchar (10)
)
AS
DECLARE @SQL varchar(2000)
SET @SQL = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[@Symbol]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[@Symbol]
CREATE TABLE [dbo].[" + @Symbol + "] (
[Ticker] [varchar](10) Null,
[Date] [date] Null,
[Open] [float] NULL ,
[High] [float] NULL ,
[Low] [float] NOT NULL ,
[Close] [float] NULL ,
[Volume] [float] NULL ,
) ON [PRIMARY]"
EXEC(@SQL)

GIVES ME A 'The identifier that starts with... is too long, maximum length is 128'

matty
Posting Yak Master

161 Posts

Posted - 2008-06-16 : 00:11:54

Specify the size of @sql..like DECLARE @Sql varchar (max)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-16 : 00:40:24

Always remember to specify a length when you use character datatype

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources