Struggling to create a VIEW in TSQL using code that works fine as a query :o(

Question

Struggling to create a VIEW in TSQL using code that works fine as a query :o(

julian.biggin

SSC Rookie

Points: 30
More actions
October 17, 2012 at 7:44 am

#283104

Hi All,
I'm trying to set up a view that will allow me to re-organise and existing table for the purposes of some reporting analysis.
The query I have works fine in the window to run general queries, but when I try to create this as a view it errors saying "Incorrect syntax near the keyword 'Declare'"
Once I've created the view, I'm then wanting to be able to perform a basic data link in a spread sheet that will pull this data directly from SQL.
I've added the query in a lower post that also contains some test data set-up scripting.
I Can't beleive it's not possible to do this, as such it's probably me approaching it in the wrong way!
Anyway, over to all you super skilled SQL experts to give me a steer in the right direction!
Cheers,
J

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

A view is a single select statement, nothing more. No variables, no parameters, no other statements.

You want either a stored procedure (recommended) or a multi-statement table-valued user-defined function (not recommended for performance reasons).

You probably also want to lose the cursor, remove the SELECT * and use column names not ordinals in your order by.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Shifting gears...

Even if you could put the code you have into a view, the code would be slow because of all the RBAR in it.

If you were to explain a bit more about what the code is trying to do and maybe post some test data (see the first link in my signature line below for how to do that), someone could probably help you write some code with much better performance that you might actually be able to use in a view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

julian.biggin SSC Rookie Points: 30 More actions · Answer 3

Thanks for the feedback so far. Okay, so what I'm actually needing to do is.......

I have two tables, the first is just the employees, the second is a transactional table that holds absence data. The records in the second table don't have independant unique sequential numbering, but even if it did I couldn't use that, as I'm only wanting certain types of entry.

So, the transaction table has single rows for each employee booking time off which holds two dates that represent a "from" and "to" date (I'm not at this point worried about weekends, bank holidays etc.) e.g. 17/10/12 to 19/10/12

If I have employees A, B and C and they have each booked three days off, I want this to show nine rows e.g.

Table shows.......

Emp Code FromDate ToDate

=====================

A Hol 15/10/12 17/10/12

B Hol 15/10/12 17/10/12

C Hol 17/10/12 19/10/12

Need it to show.......

Emp Code Date

===========

A Hol 15/10/12

A Hol 16/10/12

A Hol 17/10/12

B Hol 15/10/12

B Hol 16/10/12

B Hol 17/10/12

C Hol 17/10/12

C Hol 18/10/12

C Hol 19/10/12

I really need to be able to access this in a data link behind a spreadsheet so need a fairly simple query to go in the data source bit of the link, hence why I thought a view would be best.

Let me know if you need any more clarification.

Cheers,

J

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please read the article Jeff referred to.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

julian.biggin SSC Rookie Points: 30 More actions · Answer 5

This should create the test data needed.....

-- Drop tables if they exist

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Employee]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[Employee]

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[Absence]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[Absence]

-- Create temp table to work with

CREATE TABLE dbo.Employee (EmpId INT, U_Initials VarChar(10))

CREATE TABLE dbo.Absence (EmpId INT, Line INT, FromDate SMALLDATETIME, ToDate SMALLDATETIME, Reason VarChar(10))

--

-- Set up data for employee table

INSERT Employee VALUES(1001, 'ABC')

INSERT Employee VALUES(1005, 'XYZ')

INSERT Employee VALUES(1010, 'NBC')

-- Set up data for transaction table

INSERT Absence VALUES(1001, 1, '20121008', '20121008', 'AL')

INSERT Absence VALUES(1001, 2, '20121015', '20121017', 'AL')

INSERT Absence VALUES(1001, 3, '20121019', '20121019', 'SC')

--

INSERT Absence VALUES(1005, 1, '20121015', '20121016', 'AL')

INSERT Absence VALUES(1005, 2, '20121017', '20121017', 'SC')

INSERT Absence VALUES(1005, 3, '20121022', '20121026', 'AL')

--

INSERT Absence VALUES(1010, 1, '20121023', '20121025', 'SC')

INSERT Absence VALUES(1010, 2, '20121026', '20121026', 'AL')

---

-- List all data from Employee table

SELECT * FROM Employee

-- Lists ALL records in the table

SELECT * FROM Absence

--

-- Lists only the AL type ones that need to be expanded

SELECT * FROM Absence WHERE Reason = 'AL'

Here's the query that provides what I'm looking for, but needs to be much simpler and able to be executed within the spread sheet........

-- Drop tables if they exist

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[TempAbsence]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[TempAbsence]

--

-- Create temp table to work with

CREATE TABLE dbo.TempAbsence (ID INT, EmpId INT, EmpName VarChar(10), AbsenceDate SMALLDATETIME, AbsenceType VarChar(10))

GO

-- Define variables ready for use

DECLARE @CurrEmp VarChar(10)

DECLARE @RecCount Int; SET @RecCount = 0

DECLARE @DaysCount Int; SET @DaysCount = 0

DECLARE @ID INT

DECLARE @EmpId INT

DECLARE @EmpName VarChar(10)

DECLARE @AbsenceType VarChar(10)

DECLARE @Start SMALLDATETIME

DECLARE @End SMALLDATETIME

DECLARE @LineRef INT

--

-- Define loop for running through relevant employees

DECLARE DataSource1 CURSOR FOR

SELECT T0.U_Initials FROM Employee T0

WHERE T0.U_Initials IS NOT NULL

--

OPEN DataSource1

--

FETCH NEXT FROM DataSource1

INTO @CurrEmp

--

WHILE @@FETCH_STATUS = 0

BEGIN

--

-- Define loop for running through records for each employee

DECLARE DataSource2 CURSOR FOR

SELECT T0.Line FROM Absence T0 INNER JOIN Employee T1 ON T0.empID = T1.empID WHERE T1.U_Initials = @CurrEmp AND T0.Reason <> 'SC'

--

OPEN DataSource2

--

FETCH NEXT FROM DataSource2

INTO @LineRef

--

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @ID = T0.Line, @empid = T0.EmpId, @EmpName = T1.U_Initials, @Start = T0.FromDate, @End = T0.ToDate, @AbsenceType = T0.Reason

FROM Absence T0 INNER JOIN Employee T1 ON T0.empID = T1.empID

WHERE T1.U_Initials = @CurrEmp AND T0.Line = @LineRef

-- Get number of days in range

SET @DaysCount = DATEDIFF(d, @start, @end) + 1

-- Based off date range we will insert a record for each day

WHILE @DaysCount > 0

BEGIN

INSERT TempAbsence VALUES(@ID, @empid, @empname, DATEADD(d, @DaysCount-1, @start), @AbsenceType)

SET @DaysCount = @DaysCount-1

CONTINUE

END

SET @RecCount = @RecCount-1

FETCH NEXT FROM DataSource2

INTO @LineRef

END

--

-- Wrap up virtual data sources used

CLOSE DataSource2

DEALLOCATE DataSource2

--

-- Next bit of outer loop

FETCH NEXT FROM DataSource1

INTO @CurrEmp

END

--

-- Wrap up virtual data sources used

CLOSE DataSource1

DEALLOCATE DataSource1

--

-- View final data

SELECT * FROM TempAbsence ORDER BY 2, 1, 4

Cheers,

J

Mike.Hahn SSCarpal Tunnel Points: 4053 More actions · Answer 6

How about a CTE like this one?

create table #EmpDaysOff(

EmpID varchar(5)

,Code varchar(5)

,FromDate datetime

,ToDate datetime)

insert into #EmpDaysOff(EmpID, Code, FromDate,ToDate)

select 'A', 'Hol', '20121015','20121017'

union all select 'B', 'Hol', '20121015','20121017'

union all select 'C', 'Hol', '20121017','20121019';

with EmpDays as(

select EmpID

,Code

,FromDate DateOff

from #EmpDaysOff

union all

select ed.EmpID

,ed.Code

,ed.DateOff + 1

from empDays ed

inner join #EmpDaysOff eo

on ed.EmpID = eo.EmpID

and ed.Code = eo.Code

where ed.DateOff + 1 <= eo.ToDate

)

select *

from EmpDays

order by EmpID, DateOff

drop table #EmpDaysOff

---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

chang.hahn (10/17/2012)
How about a CTE like this one?
create table #EmpDaysOff(
EmpID varchar(5)
,Code varchar(5)
,FromDate datetime
,ToDate datetime)
insert into #EmpDaysOff(EmpID, Code, FromDate,ToDate)
select 'A', 'Hol', '20121015','20121017'
union all select 'B', 'Hol', '20121015','20121017'
union all select 'C', 'Hol', '20121017','20121019';
with EmpDays as(
select EmpID
,Code
,FromDate DateOff
from #EmpDaysOff
union all
select ed.EmpID
,ed.Code
,ed.DateOff + 1
from empDays ed
inner join #EmpDaysOff eo
on ed.EmpID = eo.EmpID
and ed.Code = eo.Code
where ed.DateOff + 1 <= eo.ToDate
)
select *
from EmpDays
order by EmpID, DateOff
drop table #EmpDaysOff

Oh, be careful now. That's a "counting" recursive CTE. Please see the following article for why those should be avoided.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

julian.biggin SSC Rookie Points: 30 More actions · Answer 8

I'm presuming following the last post that it's not the right kind of solution, but would this work as an embedded SQL query in Excel? When I've mapped this to the test data I was asked to produce, it doesn't give the same results anyway 🙁

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

For a simple solution, use a Calendar Table. There are many solutions but here is one that might help you with other queries. http://www.sqlservercentral.com/scripts/Date/68389/

With that, you can simplify your query like this:

SELECT T0.Line AS ID,

T0.EmpId,

T1.U_Initials AS empName,

Cal.Calendar_DateAS AbsenceDate,

T0.ReasonAS AbsenceType

FROM Absence T0

INNER JOIN Employee T1 ON T0.empID = T1.empID

INNER JOIN date_calendar Cal ON Cal.Calendar_Date BETWEEN T0.FromDate AND T0.ToDate

WHERE T0.Reason <> 'SC' --seems more clear to use T0.Reason = 'AL'

ORDER BY T0.EmpId, T0.Line, Cal.Cal_Date

Give it a try, I can't check right now but this could help. And this query can be turned into a view 😉

PS. For a dynamic calendar table, you can check this post http://www.sqlservercentral.com/Forums/Topic1372439-392-1.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2