SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 help with a table query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lmayer4
Starting Member

USA
31 Posts

Posted - 10/01/2013 :  09:38:09  Show Profile  Reply with Quote
This seems okay but it doesnt seem to want to run past half way through. The database name changes every month, this just creates the holding tables for it.


declare
 @sql varchar(500), @name varchar(100);
 
set
 @name = (select 'sov01_usg'
+ '' +
case 
when convert(varchar(10),getdate(),103)< '%15%' then '1'
else '2'
end+ '' +
'1'
+ '' +SUBSTRING(convert(varchar(8),getdate(),1),7,2)
+ '' +
case 
	when SUBSTRING(convert(varchar(8),getdate(),112),7,2)< '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'01'
	when SUBSTRING(convert(varchar(8),getdate(),112),7,2)>= '15' then SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 112),5,2)+ '' +'15'
end)

/****** Object:  Table [dbo].[Usage]    Script Date: 02/11/2013 20:21:35 ******/
select
@sql = 'CREATE TABLE ' + @name + '.dbo.Usage(
	[UsageID] [int] NOT NULL,
	[BatchID] [int] NOT NULL,
	[RecordNumber] [int] NOT NULL,
	[InstanceNumber] [tinyint] NOT NULL,
	[ProductID] [int] NOT NULL,
	[UsageTypeID] [smallint] NOT NULL,
	[CustID] [int] NOT NULL,
	[UsgSvcID] [int] NULL,
	[ProcessingFlags] [int] NULL,
	[CallStartTime] [datetime] NOT NULL,
	[ServiceNumber] [varchar](20) NOT NULL,
	[AcctCode] [varchar](10) NULL,
	[Duration] [int] NOT NULL,
	[ProgramID] [smallint] NOT NULL,
	[DistanceID] [tinyint] NULL,
	[Cost] [money] NULL,
	[Charge] [money] NULL,
	[OrigPlace] [varchar](16) NULL,
	[OrigLata] [smallint] NULL,
	[OrigNPA] [smallint] NULL,
	[OrigState] [varchar](2) NULL,
	[OrigCountryCode] [smallint] NULL,
	[OrigNumber] [varchar](16) NULL,
	[OrigOCN] [varchar](4) NULL,
	[TermPlace] [varchar](16) NULL,
	[TermLata] [smallint] NULL,
	[TermNPA] [smallint] NULL,
	[TermState] [varchar](2) NULL,
	[TermCountryCode] [smallint] NULL,
	[TermNumber] [varchar](16) NULL,
	[TermOCN] [varchar](4) NULL,
	[RatePeriod] [varchar](1) NULL,
	[ActualDuration] [int] NOT NULL,
	[RatePlanDetailID] [int] NULL,
	[Text1] [varchar](50) NULL,
	[OrigNetworkElementID] [varchar](5) NULL,
	[TermNetworkElementID] [varchar](5) NULL,
	[PreDiscountDuration] [int] NULL,
	[PreDiscountCharge] [money] NULL,
	[Surcharge] [money] NULL
) ON [PRIMARY]'

print (@sql)
---exec(@sql)


I keep getting the message:

Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ','.


Can anyone tell me why?

Thanks!

Laura

James K
Flowing Fount of Yak Knowledge

3761 Posts

Posted - 10/01/2013 :  10:00:32  Show Profile  Reply with Quote
Increase the length of your @sql variable
declare
 @sql varchar(MAX), @name varchar(100);
....
Go to Top of Page

lmayer4
Starting Member

USA
31 Posts

Posted - 10/01/2013 :  10:03:25  Show Profile  Reply with Quote
Thanks James, I tried max before and it didnt work but I did increase it to 5000 and it worked great. Thanks again for the help.

Laura
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000