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

Author

Topic

steven.liberman1
Starting Member

15 Posts

Posted - 2011-02-15 : 12:26:11

Topic:

Database: Version500_test!!!

New stored procedure: WEB_SO_Analyze

Parameters:
@department_id int

Resultset:
employees without rotation int
employees with rotation int
pct with rotation()

Here is the one I need help with:

USE [Version500_test!!!]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================================
-- Author: Steven Liberman
-- Create date: February 14, 2011
-- Description: WEB_SO_Analyze
-- exec WEB_SO_Analyze 2
-- ================================================================
alter procedure dbo.WEB_SO_Analyze

-- parameters name
@department_id float,
@without_rota float output,
@with_rota float output,
@pct_with_rota float output

AS
Begin

set @without_rota=20;
set @with_rota=2;
set @pct_with_rota=1;

-- select * from employee

set @without_rota =
(select count(emp_id)
from dbo.employee
where @department_id = emp_department_id and emp_dt_termination is not NuLL)

set @with_rota =
(select count(emp_id)
from dbo.employee
where @department_id = emp_department_id and emp_dt_termination is NuLL)

--final function which calculates percentage
set @pct_with_rota = (@with_rota/(@without_rota+@with_rota));

print @without_rota
print @with_rota
print @pct_with_rota
end
Go

The Commands work well, but when I try to execute it, it says the parameter @without_rota has to be supplied.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-02-15 : 12:45:28

You need to pass the parameter when calling the procedure:


DECLARE @Did int, @WO float, @W float, @pct float

SET @Did = 1

EXEC dbo.WEB_SO_Analyze @Did, @WO OUTPUT, @W OUTPUT, @pct OUTPUT

SELECT @Did, @WO, @W, @pct

Also, in the procedure
1. I suspect @department_id should be an int not a float.
2. you can use CASE to only select from employee once:


SELECT @without_rota = SUM(CASE WHEN emp_dt_termination IS NOT NULL THEN 1 ELSE 0 END)
	,@with_rota = SUM(CASE WHEN emp_dt_termination IS NULL THEN 1 ELSE 0 END)
FROM dbo.employee
WHERE emp_department_id = @department_id

steven.liberman1
Starting Member

15 Posts

Posted - 2011-02-16 : 10:11:47

Could you show me the code for this then.

steven.liberman1
Starting Member

15 Posts

Posted - 2011-02-16 : 10:41:40

SE [Version500_test!!!]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ================================================================
-- Author: Steven Liberman
-- Create date: February 14, 2011
-- Description: WEB_SO_Analyze
-- exec WEB_SO_Analyze @department_id, @without_rota output, @with_rota output, @pct_with_rota output
-- select @department_id, @without_rota, @with_rota, @pct_with_rota
-- ================================================================
alter procedure dbo.WEB_SO_Analyze

(
@department_id int
)

AS
Begin

declare @without_rota float
declare @with_rota float
declare @pct_with_rota float

set @department_id=1;
set @without_rota=20;
set @with_rota=2;
set @pct_with_rota=1;

-- select * from employee

set @department_id =
(select emp_department_id
from dbo.employee)

set @without_rota =
(select count(emp_id)
from dbo.employee
where @department_id = emp_department_id and emp_dt_termination is not NuLL)

set @with_rota =
(select count(emp_id)
from dbo.employee
where @department_id = emp_department_id and emp_dt_termination is NuLL)

--final function which calculates percentage
set @pct_with_rota = (@with_rota/(@without_rota+@with_rota));

print @without_rota
print @with_rota
print @pct_with_rota
end
Go

This is my code now,
but it does not execute the stored prox.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources