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
 General SQL Server Forums
 New to SQL Server Programming
 view error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/04/2007 :  07:36:39  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
5514 Posts

Posted - 05/04/2007 :  07:39:19  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 05/04/2007 07:39:50
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/07/2007 :  00:39:04  Show Profile  Reply with Quote
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
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 05/07/2007 :  00:42:49  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

pbguy
Constraint Violating Yak Guru

India
319 Posts

Posted - 05/07/2007 :  00:43:22  Show Profile  Reply with Quote
after drop voew u should tell the sql server that the batch is completed ...

put Go after drop view
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/07/2007 :  00:49:22  Show Profile  Reply with Quote
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
Go to Top of Page

pbguy
Constraint Violating Yak Guru

India
319 Posts

Posted - 05/07/2007 :  00:52:24  Show Profile  Reply with Quote
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
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/07/2007 :  00:56:25  Show Profile  Reply with Quote
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
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/07/2007 :  06:00:54  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/07/2007 :  06:17:02  Show Profile  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5514 Posts

Posted - 05/07/2007 :  06:24:40  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/07/2007 :  06:25:32  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/07/2007 :  06:28:50  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5514 Posts

Posted - 05/07/2007 :  07:07:07  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
OP wants to create view from inside stored procedure, which is not possible.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"
Go to Top of Page

jogin malathi
Posting Yak Master

India
117 Posts

Posted - 05/07/2007 :  07:39:40  Show Profile  Reply with Quote
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
Go to Top of Page

sidpad
Starting Member

1 Posts

Posted - 05/16/2007 :  15:57:39  Show Profile  Reply with Quote
Hi Malathi Rao,

Can you please how did you do it?

Sid
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.17 seconds. Powered By: Snitz Forums 2000