Stored procedure - SQL Server Forums

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

Stored procedure

Author

Topic

kkrishna
Starting Member

23 Posts

Posted - 2009-07-07 : 18:03:56

I am trying to create a stored procedure for the AdventureWorks database named uspEmployeeAgeTrend. When executed, this will display a view which contains EmployeeID,Gender and a calculated column of the employee age.
Here is the code that I compiled:

CREATE PROCEDURE uspEmployeeAgeTrend
@EmpAge int
AS
BEGIN
-- If the named view exists, delete the same and create the view.
IF dbo.ObjectExists('HumanResources.vEmployeeAge','V')= 1
DROP VIEW HumanResources.vEmployeeAge;
-- Calculate the variable.
CREATE VIEW HumanResources.vEmployeeAge AS
SELECT emp.EmployeeID,emp.Gender,@EmpAge
FROM HumanResources.Employee AS emp
WHERE @EmpAge = ROUND(DATEDIFF(day,emp.BirthDate,GetDate())/365,0)
END

I get the error message:
Incorrect syntax near the keyword 'VIEW'.

It could be that CREATE VIEW.. should be the first statement in the query. I am at a loss how to rearrange the code.

Please advise me how I go about it.
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-07 : 18:09:35

quote:
Originally posted by kkrishna

Here is the code that I compiled:

CREATE PROCEDURE uspEmployeeAgeTrend
@EmpAge int
AS
BEGIN
-- If the named view exists, delete the same and create the view.
IF dbo.ObjectExists('HumanResources.vEmployeeAge','V')= 1
DROP VIEW HumanResources.vEmployeeAge;
-- Calculate the variable.
CREATE VIEW HumanResources.vEmployeeAge AS
SELECT emp.EmployeeID,emp.Gender,@EmpAge
FROM HumanResources.Employee AS emp
WHERE @EmpAge = ROUND(DATEDIFF(day,emp.BirthDate,GetDate())/365,0)
END

kkrishna
Starting Member

23 Posts

Posted - 2009-07-08 : 00:04:51

Thanks Lamprey for the quick reply.

I modified the code further as following:

ALTER PROCEDURE [dbo].[uspEmployeeAgeTrend]
AS
BEGIN

SELECT emp.EmployeeID,emp.Gender,
ROUND(DATEDIFF(day,emp.BirthDate,GetDate())/365,0) AS EmployeeAge
FROM HumanResources.Employee AS emp

END

When I run the EXEC uspEmployeeAgeTrend, I get a table with three columns with the newly created column EmployeeAge. Now I want to find the Max, Min and Avg ages of the employees from this table.
How can I add code to the EXEC... code line so that I can get the outputs?

- Krishna

kkrishna
Starting Member

23 Posts

Posted - 2009-07-09 : 12:59:30

Hi All,

Will someone advise me on the point raised?
Thanks in advance.
- Krishna

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-09 : 13:25:33

I'm not sure where you want to use those aggregate functions (inside or outside your proc... but if you wanted to use tehm inside, just use your existing query and use it as a derived table:

SELECT MAX(EmployeeAge), MIN(EmployeeAge), AVG(EmployeeAge)
FROM
(
	SELECT emp.EmployeeID,emp.Gender,
	ROUND(DATEDIFF(day,emp.BirthDate,GetDate())/365,0) AS EmployeeAge
	FROM HumanResources.Employee AS emp
)as T

Or you could insert the results of your currect stored procedure into a temp table and query from that..

kkrishna
Starting Member

23 Posts

Posted - 2009-07-09 : 18:13:51

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources