view error - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

view error

Author

Topic

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-04 : 07:36:39

Hi all
iam creating view as follows
if exists drop view v1
create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date

i want to check before creatuing view but i gives erros as follows
Incorrect syntax near the keyword 'drop'.
'CREATE VIEW' must be the first statement in a query batch.
No rows affected.

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-04 : 07:39:19

[code]if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v1]
GO

create view dbo.v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-07 : 00:39:04

quote:

Originally posted by harsh_athalye

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v1]
GO

create view dbo.v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.v1') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.v1

create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date

If iam saving the above query it shows the following error

Incorrect syntax near keyword "view"

Malathi Rao

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 00:42:49

You need the keywords "GO" in between the DROP and the CREATE.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 00:43:22

after drop voew u should tell the sql server that the batch is completed ...

put Go after drop view

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-07 : 00:49:22

quote:
Originally posted by pbguy

after drop voew u should tell the sql server that the batch is completed ...

put Go after drop view

Ya i tried with go after drop statement
Again it shows the same error as
incorrect syntax near "go"

Malathi Rao

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 00:52:24

put go in the new line...do not put go along with drop view syntax

like...

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.v1') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.v1
go
create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-07 : 00:56:25

quote:
Originally posted by pbguy

put go in the new line...do not put go along with drop view syntax

like...

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.v1') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.v1
go
create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date

ALTER PROCEDURE dbo.StoredProcedure2

AS

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.v1') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.v1
go
create view v1 as
Select Employee_id ,round((datediff(m,min(start_date),getdate()))/12.0,1) as Years from Sagarsoft.dbo.o_employeeexp (nolock) group by employee_id,start_date

Ya i wrote the stored procs as above

it shows the same error
And iam executing stored procs in SQL SERVER 2005 INTEGRATED WITH .NET IN VISUAL STUDIO

Malathi Rao

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:00:54

As far I know, Go keyword can only be used when you are running the
queries in the Query Analyser or SSMS, for any other enviorment it will
not work, unless there is some special programming done for it.

Chirag

http://chirikworld.blogspot.com/

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-07 : 06:17:02

quote:
Originally posted by chiragkhabaria

As far I know, Go keyword can only be used when you are running the
queries in the Query Analyser or SSMS, for any other enviorment it will
not work, unless there is some special programming done for it.

Chirag

http://chirikworld.blogspot.com/

ALTER PROCEDURE usp_r_search_internal
@eid int
,@head_line varchar(150)
,@func_area varchar(150)
,@loc varchar(50)
,@exp varchar(50)
,@k_skills varchar(50)
,@emp varchar(50)
,@operator char(1)

AS

delete from tempemployer
declare @eno varchar(50)
declare @edu1 varchar(150),@edu2 varchar(150),@education varchar(max),@skill varchar(150)
,@f_area varchar(max),@years varchar(50),@salary varchar(50),@location varchar(50)
declare @employer varchar(150)
declare dc1 cursor for select Employee_id from sagarsoft.dbo.o_employee
begin
open dc1
fetch dc1 into @eno
while @@fetch_status=0
begin
set @employer=''
set @education=''
set @edu1=''
set @edu2=''
set @skill=''
set @f_area=''
set @salary = ''
set @location =''

select @employer= case @employer when '' then employer
else @employer + ','+ employer end
from sagarsoft.dbo.o_employeeexp (nolock) where employee_id=@eno

select @edu1= w.Education_Name from Sagarsoft.dbo.o_Education e (nolock)
join Sagarsoft.dbo.O_EducationDetails w (nolock) on w.Education_Id=e.Education_Id
where Education_End_Date=(select max(Education_End_Date) from Sagarsoft.dbo.o_Education (nolock) where employee_id=@eno)

select @edu2= w.Education_Name from Sagarsoft.dbo.o_Education e (nolock)
join Sagarsoft.dbo.O_EducationDetails w (nolock) on w.Education_Id=e.Education_Id
where Education_End_Date=(select max(Education_End_Date) from Sagarsoft.dbo.o_Education (nolock)
where Education_End_Date<(select max(Education_End_Date) from Sagarsoft.dbo.o_Education (nolock) where employee_id=@eno )and employee_id=@eno)

select @Skill= case @skill when '' then n.skill_Name
else @skill + ','+ n.skill_Name end,
@f_area= case @f_area when '' then s.Functiional_Area
else @f_area + ','+ s.Functiional_Area end
from sagarsoft.dbo.o_skills s (nolock)
left join Sagarsoft.dbo.o_skillsdetails n (nolock) on n.Skill_id=s.skill_id
where employee_id=@eno

Select @years=round((datediff(m,min(start_date),getdate()))/12.0,1) from Sagarsoft.dbo.o_employeeexp (nolock) where employee_id=@eno

Select @salary =Amount from sagarsoft.dbo.o_Currentjob (nolock) where employee_id=@eno
Select @location = Citytown from sagarsoft.dbo.o_Contacts (nolock) where employee_id=@eno

if(@edu2='')
begin
set @education=@edu1
end
else
begin
set @education= @edu1 +','+ @edu2

if(@education =',')
begin
set @education=''
end
end
--print @edu

insert into tempemployer values(@eno,@employer,@education,@Skill,@f_area,Round(@years,1),@salary,@location)

fetch dc1 into @eno
end
close dc1
deallocate dc1
end

----creating view to store each employee resume details
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v2]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v2]

create view v2 as
select 'Employer' as RecordType,id As Employee_Id,Employer,Salary,Functional_Area,Skills,Location, Years,Education,E.Employee_First_Name+' ' +E.Employee_Last_Name as Ename
from RPO.dbo.tempemployer T
Left join Sagarsoft.dbo.o_Employee E on E.Employee_id=T.id
Union all
select 'Functional_Area' as RecordType,id AS Employee_Id,Employer,Salary,Functional_Area,Skills,Location, Years,Education,E.Employee_First_Name+' ' +E.Employee_Last_Name as Ename
from RPO.dbo. tempemployer T
Left join Sagarsoft.dbo.o_Employee E on E.Employee_id=T.id
union all
select 'Skills' as RecordType,id AS Employee_Id,Employer,Salary,Functional_Area,Skills,Location, Years,Education,E.Employee_First_Name+' ' +E.Employee_Last_Name as Ename
from RPO.dbo.tempemployer T
Left join Sagarsoft.dbo.o_Employee E on E.Employee_id=T.id
union all
select 'Location' as RecordType,id AS Employee_Id,Employer,Salary,Functional_Area,Skills,Location, Years,Education,E.Employee_First_Name+' ' +E.Employee_Last_Name as Ename
from RPO.dbo.tempemployer T
Left join Sagarsoft.dbo.o_Employee E on E.Employee_id=T.id
Union all
select 'Experience' as RecordType,id as Employee_id,Employer,Salary,Functional_Area,Skills,Location, Years,Education,E.Employee_First_Name+' ' +E.Employee_Last_Name as Ename
from RPO.dbo.tempemployer T
Left join Sagarsoft.dbo.o_Employee E on E.Employee_id=t.id
---end of the View

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v1]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v1]

create view v1 as
select distinct Employee_Id, Isnull(Employer,' ' ) + isnull(Functional_Area,' ')+isnull(Skills,' ') + isnull(Location,' ') + isnull(Years,' ') as Keywords from v2

--------Stored proce to search internal resource using multi search---------

if(@operator='A')
BEGIN
declare @keyword nvarchar(max)
declare @sql nvarchar(1000)
--declare @key nvarchar(max)
if(@head_line !='')
begin
SELECT v2.Employee_Id,Employer,Salary,Education,Location From RPO.dbo.V2 (nolock)
join RPO.dbo.V1 on V1.Employee_id=v2.employee_id
where Keywords like '%'+@head_line+'%'
end
else
select @sql=
'SELECT v2.Employee_Id,Employer,Salary,Education,Location From RPO.dbo.V2 (nolock)
where 1=1'
print @sql

IF @func_area !=''
begin
SELECT @sql = @sql + ' and Functiional_Area like ''%''+'''+@func_area+'''+''%'''
print'2'
print @sql
end

IF @loc !=''
begin
SELECT @sql = @sql + ' and Location like ''%''+'''+@loc+'''+''%'''
print'3'
print @sql
end

IF @exp !=''
begin
SELECT @sql = @sql + ' and Total_exp like ''%''+'''+@exp+'''+''%'''
print'4'
print @sql
end

IF @k_skills !=''
begin
SELECT @sql = @sql + ' and Skills like ''%''+'''+@k_skills+'''+''%'''
print'5'
print @sql
end

IF @emp !=''
begin
if @eid=1
begin
SELECT @sql = @sql + ' and Employer like ''%''+'''+@emp+'''+''%'''--='''+@employer+''''
print'6'
print @sql
end
else
begin
SELECT @sql = @sql + ' and Employer ='''+@emp+''''
print'66'
print @sql
end
end

exec sp_executesql @sql
END

If iam storing the above procs it shows errors as invalid syntax near 'view'

Malathi Rao

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 06:24:40

You can't create objects like View/SP/function inside a Stored Proc (except using D-Sql), neither you should do it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:25:32

There are 2 options

1) Run each scripts sepeartely, like run the Alter of the Script and then run the Create View and so on and so forth.

2) Else at the run time, take the Consider Go as the seperater between two scripts and run the between script.

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:28:50

quote:
Originally posted by harsh_athalye

You can't create objects like View/SP/function inside a Stored Proc (except using D-Sql), neither you should do it.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Dude, Is OP trying to create the script or trying to run the script from another enviorment??

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 07:07:07

OP wants to create view from inside stored procedure, which is not possible.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jogin malathi
Posting Yak Master

117 Posts

Posted - 2007-05-07 : 07:39:40

quote:
Originally posted by harsh_athalye

OP wants to create view from inside stored procedure, which is not possible.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Thanks harsh i got the solution for my problem

Malathi Rao

sidpad
Starting Member

1 Post

Posted - 2007-05-16 : 15:57:39

Hi Malathi Rao,

Can you please how did you do it?

Sid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources