Passing values from a VB form into a SQL Stored Procedure with output to VB Report Viewer

Transact-SQL
1

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.... :frowning:

Thank you everyone for viewing this and thanks in advance for any assistance you can offer. :smile:

2

That won't work. in SQL, nothing "equals" null. If you want to test for null, use the "is" operand as in:

if @day is null

However, in your test example, that won't work either. You are not passing null for @day, you are passing an empty string. Not only that, you are forcing SQL to implicitly convert strings to integers (which it will do). that's just extra work.

SO...change your if @x = null lines to if @x is null and change your procedure call to:

EXEC procurementGoodsPROC 10, null, 2015;

3

That's it!
It works now!

Thank you so much!!!

4

/* SQL 2008
Visual Studio 2013 */

Ok my next step is to figure out how to:

(A) Receive the values as user input from my VB form --AND Pass the values ---> to my SQL Stored Procedure.

(B) The ROWS from the stored procedure are DISPLAYED IN Visual Studio's VB "REPORT VIEWER"
*** Report Viewer is triggered to open when the user presses a button on my VB form after they have entered the values.. ***

NOTE: I've found in visual studio that SQL EXEC statements don't work when trying to "add a query" to the "table adapter" for the report viewer .

Any pointers SQL Team can give me to research on my own would be great.

5

Actually you're entering into Visual Studio territory. There are excellent VS forums

6

Thanks Gbritton! :smile: