Create View from Stored Procedure

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 from Stored Procedure

Author

Topic

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-09 : 13:31:02

Using Server OS 2003, and SQL Server 2005 Server Management Studio I created the following stored procedure:

USE [campusvue]
GO
/****** Object: StoredProcedure [dbo].[AddedStudent] Script Date: 12/09/2010 10:18:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MBergeron
-- Create date: 12/08/2010
-- Description: Number of Students Added Per Day to SyStudent Table
-- =============================================
ALTER PROCEDURE [dbo].[AddedStudent]
AS
-- Add the parameters for the stored procedure here
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

SELECT DATENAME(weekday, DateAdded) AS AddDate, DATEPART(wk, DateAdded) AS WeekNumber,
DATEPART(year, DateAdded)AS YearNumber,
COUNT(SyStudentID) AS NewStudents
FROM dbo.SyStudent
WHERE
dbo.SyStudent.DateAdded BETWEEN
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
GROUP BY
DATENAME(weekday, DateAdded), DATEPART(wk, DateAdded), DATEPART(year, DateAdded)
ORDER BY
YearNumber,
WeekNumber,
AddDate

When I execute the procedure I get data similar to this:

AddDate= Friday
WeekNumber= 49
YearNumber=2010
NewStudents=47

AddDate=Monday
WeekNumber=49
YearNumber=2010
NewStudents=64

This is the data that I need. However, I am trying to take this information and place it in an Excel SpreadSheet.

I need to create a view on a different SQL server which links back to the view I would like to create from this data outpu.

Can anyone help me with the syntax to get this stored procedure output either in a table or as a view? Any assistance is greatly appreciated.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-09 : 14:03:54

for getting stored procedure result into table use either of below

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-09 : 15:06:04

USE [campusvue]
GO
/****** Object: StoredProcedure [dbo].[AddedStudent] Script Date: 12/09/2010 10:18:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: MBergeron
-- Create date: 12/08/2010
-- Description: Number of Students Added Per Day to SyStudent Table
-- =============================================
ALTER PROCEDURE [dbo].[AddedStudent]
AS
-- Add the parameters for the stored procedure here
DECLARE @TodayDayOfWeek INT
DECLARE @EndOfPrevWeek DateTime
DECLARE @StartOfPrevWeek DateTime

--get number of a current day (1-Monday, 2-Tuesday... 7-Sunday)
SET @TodayDayOfWeek = datepart(dw, GetDate())
--get the last day of the previous week (last Sunday)
SET @EndOfPrevWeek = DATEADD(dd, -@TodayDayOfWeek, GetDate())
--get the first day of the previous week (the Monday before last)
SET @StartOfPrevWeek = DATEADD(dd, -(@TodayDayOfWeek+6), GetDate())

SELECT DATENAME(weekday, DateAdded) AS AddDate, DATEPART(wk, DateAdded) AS WeekNumber,
DATEPART(year, DateAdded)AS YearNumber,
COUNT(SyStudentID) AS NewStudents
FROM dbo.SyStudent
WHERE
dbo.SyStudent.DateAdded BETWEEN
CONVERT(VARCHAR, @StartOfPrevWeek,7)
AND
CONVERT(VARCHAR, @EndOfPrevWeek+1,7)
GROUP BY
DATENAME(weekday, DateAdded), DATEPART(wk, DateAdded), DATEPART(year, DateAdded)
ORDER BY
YearNumber,
WeekNumber,
AddDate

CREATE Table dbo.StudentsAdded1 (DATENAME(weekday, DateAdded), DATEPART(wk, DateAdded), DATEPART(year, DateAdded))
INSERT INTO
dbo.StudentsAdded1 (DATENAME(weekday, DateAdded), DATEPART(wk, DateAdded), DATEPART(year, DateAdded))
EXEC
[dbo].[AddedStudent]

and I get these errors:

Msg 173, Level 15, State 1, Procedure AddedStudent, Line 36
The definition for column 'DATENAME' must include a data type.
Msg 102, Level 15, State 1, Procedure AddedStudent, Line 38
Incorrect syntax near '('.

Am I supposed to create a new query for the temp table or can I execute within the view? If so, do I have the script in the proper place within the procedure?

This is my first stored procedure and I have been researching this for days. I just can't seem to get it to work.

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-09 : 15:07:27

Pardon me. Execute within the Procedure not "view".

meberg66219
Yak Posting Veteran

65 Posts

Posted - 2010-12-13 : 11:29:16

Could someone please assist me with this syntax for creating a table from the stored procedure above:

CREATE TABLE
NewStudents2(AddDate nvarchar, WeekNumber nvarchar, YearNumber nvarchar, NewStudents nvarchar)
INSERT INTO
NewStudents2 EXEC dbo.AddedStudent
DECLARE @length nvarchar
SELECT @length = syscolumns.length from syscolumns, sysobjects where
syscolumns.id = sysobjects.id and sysobjects.name = 'AddStudent' and
syscolumns.name = 'NewStudents'

SELECT * FROM AddStudent where (LEN(NewStudents)+
LEN(AddDate))>@length

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources