Creating a new 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

Creating a new procedure

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-30 : 14:35:51

I have written a script where now I am trying to create it into a procedure. I am using several date variables and the script is not compiling.. What am I doing wrong??

Here is the a few lines of the script with the create statement

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_UNDERWRITING_AUDIT_REPORT') begin
DROP PROC usp_UNDERWRITING_AUDIT_REPORT
end
GO
CREATE PROC usp_UNDERWRITING_AUDIT_REPORT (@LOW_DATE DATETIME,@HIGH_DATE DATETIME, @StartOfMonth DATETIME,@EndOfMonth DATETIME ) AS

-- DECLARE @HIGH_DATE DATETIME, @LOW_DATE DATETIME,@StartOfMonth DATETIME,@EndOfMonth DATETIME
SET @LOW_DATE= '1/1/12';
SET @HIGH_DATE= '7/1/12';
SET @StartOfMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE), 0);
SET @EndOfMonth = DATEADD(DAY, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE) + 1, 0));

When I try executing the script

---EXEC usp_UNDERWRITING_AUDIT_REPORT '1/1/12','7/1/12'

it gives me the following error

Procedure or function 'usp_UNDERWRITING_AUDIT_REPORT' expects parameter '@StartOfMonth', which was not supplied.

but the '@StartOfMonth' is a calculated field..

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-30 : 14:40:05

Then remove it from the parameters list. It is expecting you to pass it in.

Also, as it is, you are setting all of your parameters in the procedure, so anything passed in is going to be overwritten.

-Chad

divan
Posting Yak Master

153 Posts

Posted - 2012-08-30 : 14:45:13

Chad thank you for the quick response but if I do not declare it in the parameter list then I get the following error:

Must declare the scalar variable "@StartOfMonth".

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-30 : 15:03:29

CREATE PROC usp_UNDERWRITING_AUDIT_REPORT (@LOW_DATE DATETIME,@HIGH_DATE DATETIME) AS

DECLARE @StartOfMonth DATETIME
DECLARE @EndOfMonth DATETIME
SET @StartOfMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE), 0);
SET @EndOfMonth = DATEADD(DAY, - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @HIGH_DATE) + 1, 0));

....

-Chad

divan
Posting Yak Master

153 Posts

Posted - 2012-08-30 : 15:05:59

Thanks Chad... I am learning something new everyday...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 15:38:23

see this to understand how dateadd,datediff logic works

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources