Hi If I try to run the code below I get the following errors
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'view'.
Server: Msg 170, Level 15, State 1, Line 51
Line 51: Incorrect syntax near '@.month1'.
I am not sure why it does not like the keyword view ? also I am trying to use the variables in the column name of the create table but again it does not like this.
-- declare all variables!
DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char
-- declare the cursor
DECLARE call_data CURSOR FOR
SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3
OPEN call_data
FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3
BEGIN
--run SQL statements
drop view temp_view
create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1
drop table temp_table
create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money,
@.month2 money,
@.month3 money
)
END
CLOSE call_data
DEALLOCATE call_data
RETURN
For starters, the CREATE VIEW statement has to be the first statement in a batch so it can't be used in this way.
You could dynamically create your CREATE VIEW statement then use sp_executesql to execute the command.
Chris
|||Hi thanks for the reply
I am quite new to this what do you mean by dynamically create the CREATE VIEW statement ?
|||DECLARE @.startdate datetime,
@.enddate datetime,
@.enddate1 datetime,
@.month1 char,
@.month2 char,
@.month3 char
-- declare the cursor
DECLARE call_data CURSOR FOR
SELECT dbo.removetime(DATEADD(month, -3, getdate())) as startdate,
dbo.removetime(DATEADD(month, -2, getdate())-1) as enddate,
dbo.removetime(DATEADD(month, 0, getdate())-1) as enddate1,
left(dbo.removetime(DATEADD(month, -3, getdate())),5) as month1,
left( dbo.removetime(DATEADD(month, -2, getdate())-1),4) as month2,
left(dbo.removetime(DATEADD(month, 0, getdate())-1),4) as month3
OPEN call_data
FETCH call_data INTO @.startdate,
@.enddate,
@.enddate1,
@.month1,
@.month2,
@.month3
BEGIN
--run SQL statements
drop view temp_view
create view temp_view as
select column1,column2
from some_table
where date_and_time >= @.startdate -- you cant have variabl inside view
and date_and_time <= @.enddate1 -- you cant have variabl inside view
drop table temp_table
create table temp_table (
account_no int,
account_holder_surname varchar(80),
account_holder_forename varchar(80),
@.month1 money, -- Remove @.
@.month2 money, -- Remove @.
@.month3 money -- Remove @.
)
END
CLOSE call_data
DEALLOCATE call_data
RETURN
and also tell us what u are intended to do... there are couple of wrong sysntax in the script... you can not use variable inside a view definition...
Madhu
|||Hi
First of I am trying to create a view of data between a specific date range. This is the last three months. So if it was to run today then the view would contain data from 1st nov 2006 to 28th feb 2007.
When it runs on the 1st Apr the view would contain data from 1st Dec to 31st march and so on. This is why I am trying to drop the view first then create it. I am then running a query on the view and inserting the results of this into the table i create in the script.
Secondly as I am working with a rolling 3 months of data I need to drop the table I insert the data into an create it again with the correct column headings ie NOV,DEC,JAN. This is why I have the variables in the create view and create table statements.
If this is not possible is their an other way of doing this ?
Hope that makes sense
|||
I wouldn't bother creating a View for temporary purposes, you can use the SELECT statement as is to directly insert data into tables.
Try the code below to drop and create your table - I see no need for a cursor in the scenario you have presented.
Chris
DECLARE @.startdate DATETIME
DECLARE @.enddate DATETIME
DECLARE @.enddate1 DATETIME
DECLARE @.month1 NVARCHAR(10)
DECLARE @.month2 NVARCHAR(10)
DECLARE @.month3 NVARCHAR(10)
DECLARE @.TableName NVARCHAR(100)
DECLARE @.SQLString NVARCHAR(4000)
SELECT @.startdate = dbo.removetime(DATEADD(month, -3, getdate())),
@.enddate = dbo.removetime(DATEADD(month, -2, getdate())-1),
@.enddate1 = dbo.removetime(DATEADD(month, 0, getdate())-1),
@.month1 = left(dbo.removetime(DATEADD(month, -3, getdate())),5),
@.month2 = left( dbo.removetime(DATEADD(month, -2, getdate())-1),4),
@.month3 = left(dbo.removetime(DATEADD(month, 0, getdate())-1),4)
--The name of the new table
SELECT @.TableName = N'Temp_Table'
--Build the string to drop the table
SET @.SQLString =
N'IF OBJECT_ID(' + QUOTENAME(@.TableName, '''') + ') IS NOT NULL DROP TABLE [' + @.TableName + '];'
--Build the string to create the table
SET @.SQLString = @.SQLString +
N'CREATE TABLE [' + @.TableName + ']
(
[account_no] int,
[account_holder_surname] varchar(80),
[account_holder_forename] varchar(80),
[' + @.month1 + '] money,
[' + @.month2 + '] money,
[' + @.month3 + '] money
)'
--Execute the statements
EXEC(@.SQLString)
--Prove that the new table exists
--EXEC sp_help 'Temp_Table'
/*
--Don't create a 'temporary' view - just use the query directly like this...
INSERT INTO.... / UPDATE etc...
select column1,column2
from some_table
where date_and_time >= @.startdate
and date_and_time <= @.enddate1
*/
|||Hi Chris
Thanks so much. It was the syntax around the create table I could not get my head around !
Thanks
min ca