MS SQL 2008
Hi,
I'm a novice trying to make the following work in SQL management studio before moving on to phase II where I'll be trying to pass parameters into to this sql PROC from a vb form.
Sample Data:
Date Column
2015-09-04 09:01:00
2015-10-09 09:01:00
2015-10-09 09:01:00
2015-10-09 09:01:00
2015-10-09 17:17:00
2015-09-22 17:22:00
2015-10-22 07:11:00
2015-10-02 11:48:00
2015-10-06 11:51:00
2015-10-29 12:05:00
SQL Team: The following select statement is what I'm trying to convert into a stored procedure where @parameters can be passed into the PROC instead of hard coding month (9) and year (2015) values.
SELECT * FROM procurement_goods
WHERE MONTH(date) = 9 AND YEAR(date) = 2015
date column
2015-09-22 17:22:00
2015-09-04 09:01:00
SQL Team: Here is my attempt to create the proc......
ALTER PROCEDURE [dbo].[procurementGoodsPROC]
(
@month int,
@day int,
@year int
)
AS
BEGIN
SET NOCOUNT ON;
-- *************************
-- * First Check
-- **************************
-- if @month = a value
-- if @year = a value
-- but @day = " "
if @day = null SELECT * FROM procurement_goods WHERE MONTH(date) = @month AND YEAR(date) = @year
-- *******************************
-- * Second Check
-- *******************************
-- if @year has a value
-- but @month = " "
-- and @day = " "
else if @month = null and @day = null SELECT * FROM procurement_goods WHERE YEAR(date) = @year
-- ****************************
-- * Third check for combination of both missing @month and @year
-- ***************************
else if @year = null and @month = null print 'You must enter a year'
-- ***************************
-- * Last check for a missing @year
-- ***************************
else if @year = null print 'You must enter a Year.'
END
SQL Team, here is how I execute the procedure from management studio
But I get no rows.
USE [cacao]
GO
EXEC procurementGoodsPROC '10','','2015'
GO
Here's what management studio displays.
Command(s) completed successfully.
But no rows are displayed....
Thank you everyone for viewing this and thanks in advance for any assistance you can offer.