..... New and I can use your help

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

..... New and I can use your help

Author

Topic

dbserver2000
Starting Member

47 Posts

Posted - 2009-02-15 : 10:07:44

I am trying to follow an exercise to create a view in T-SQL
I get this error message:

Msg 156, Level 15, State 1, Procedure vw_Empsalary, Line 7
Incorrect syntax near the keyword 'Select'.

here is the T-SQL:
USE Employee
GO
CREATE VIEW dbo.vw_Empsalary
AS
SELECT TOP (100) PERCENT EmpID, LName, FName, Salary
FROM dbo.Employees
ORDER BY LName

Select * from vw_EmpSalary

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-15 : 10:22:00

This will make the syntax error go away.

USE Employee
GO
CREATE VIEW dbo.vw_Empsalary
AS
SELECT TOP (100) PERCENT EmpID, LName, FName, Salary
FROM dbo.Employees
ORDER BY LName
GO

Select * from vw_EmpSalary

But...
If you are using SQL 2005 or higher, this may not work as you expect. So instead, do this:

USE Employee
GO
CREATE VIEW dbo.vw_Empsalary
AS
SELECT EmpID, LName, FName, Salary
FROM dbo.Employees
GO

Select * from vw_EmpSalary ORDER BY LName

This is because SQL 2005 will optimize away the "top 100 percent" and "order by" clause in the view. So your results may not be ordered as you expect.

dbserver2000
Starting Member

47 Posts

Posted - 2009-02-15 : 23:57:59

Thank yo so much for your help, BUT, now I have a different Error !

Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_EmpSalary'.

Now where is my mistake, I can not figure it out !

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 00:04:24

first create view and then execute the select statement

r check the the databasename in ur query where ur are executing

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-16 : 00:18:54

U can execute the view statements and select * from viewname together by using GO Statement after the view statements

Jai Krishna

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources