create View error at 'Declare'

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

SQL Server 2005 Forums

Transact-SQL (2005)

create View error at 'Declare'

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2009-05-12 : 15:23:46

I get this error:
Msg 156, Level 15, State 1, Procedure VW_Well_Service_PayrollView, Line 5
Incorrect syntax near the keyword 'Declare'.

when trying to run this code:

USE [MercerDailyWorkTicket]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

IF OBJECT_ID('dbo.VW_Well_Service_PayrollView') IS NOT NULL
DROP VIEW [dbo].[VW_Well_Service_PayrollView];
GO
CREATE VIEW [dbo].[VW_Well_Service_PayrollView]
AS

Declare @Startdate datetime
Declare @WorkLoc nvarchar (10)
set @Startdate = '05/09/2009'
set @WorkLoc ='MWSBOW'
;

SELECT
ROW_NUMBER()OVER (PARTITION BY dtl.PayWeek,dtl.EmployeeNumber ORDER BY dtl.Date)TransNumber,
dtl.Date, dtl.EmployeeNumber, dtl.EmployeeName, dtl.JobTitle, dtl.Hours,
CASE WHEN PayCategory IN ('VAC','HOL') THEN 0 ELSE Hours END AS Hours1,
dtl.CostCenter, dtl.PayCategory, dtl.PayWeek, hdr.WorkLocation, hdr.TimeClockID, hdr.Status,
hdr.StartDate, hdr.Entity, hdr.GLType, hdr.PayGroup, hdr.PayGroupCode, hdr.LocationGLCode

FROM dbo.VW_WellService_PayrollView AS dtl INNER JOIN
dbo.PayrollHdr AS hdr ON dtl.EmployeeNumber = hdr.EmployeeNumber
WHERE Date BETWEEN Dateadd(Day,-14,@Startdate) and @Startdate AND hdr.WorkLocation=@WorkLoc

GO

would anyone know why i'm pulling an error? thanks.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-05-12 : 15:50:30

You can't have DECLARE in a view...a view is a simple select only. If you need parameters to your query you can create a table valued function instead:

CREATE FUNCTION [dbo].[VW_Well_Service_PayrollView] (
  @Startdate datetime,
  @WorkLoc nvarchar (10)
)
RETURNS TABLE
AS RETURN (

SELECT
ROW_NUMBER()OVER (PARTITION BY dtl.PayWeek,dtl.EmployeeNumber ORDER BY dtl.Date)TransNumber,
dtl.Date, dtl.EmployeeNumber, dtl.EmployeeName, dtl.JobTitle, dtl.Hours,
CASE WHEN PayCategory IN ('VAC','HOL') THEN 0 ELSE Hours END AS Hours1,
dtl.CostCenter, dtl.PayCategory, dtl.PayWeek, hdr.WorkLocation, hdr.TimeClockID, hdr.Status,
hdr.StartDate, hdr.Entity, hdr.GLType, hdr.PayGroup, hdr.PayGroupCode, hdr.LocationGLCode

FROM dbo.VW_WellService_PayrollView AS dtl INNER JOIN
dbo.PayrollHdr AS hdr ON dtl.EmployeeNumber = hdr.EmployeeNumber
WHERE Date BETWEEN Dateadd(Day,-14,@Startdate) and @Startdate AND hdr.WorkLocation=@WorkLoc
)

Then get your results by doing a

SELECT * FROM dbo.VW_Well_Service_PayrollView('05/09/2009', 'MWSBOW')

- Lumbago

osupratt
Posting Yak Master

238 Posts

Posted - 2009-06-08 : 10:28:20

thanks Lumbago for your help. i did create this function. now if i want to have a procedure that has the two parameters @StartDate and @WorkLoc..... and have these values set in a report, can i do this?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-09 : 04:09:08

Did you notice this:

> SELECT * FROM dbo.VW_Well_Service_PayrollView('05/09/2009', 'MWSBOW')

This is a normal select, you can use it any way you like.

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources