EXECUTE 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

SQL Server 2008 Forums

Transact-SQL (2008)

EXECUTE PROCEDURE

Author

Topic

Tklaversma
Starting Member

1 Post

Posted - 2011-06-05 : 09:11:25

Hi all!

Have a question regarding my CREATE PROCEDURE. First things first, below you can view my code.


USE [OutdoorParadise]
GO

CREATE PROCEDURE [dbo].[ControleerVoorraad] (
	@WarehouseCode int,
	@ProductNumber int,
	@Message varchar(100) OUT
)

AS

BEGIN
	-- Preventing extra result sets from interfering with SELECT statements
	SET NOCOUNT ON;
	
	-- Variables
	DECLARE @Aantal_Free int;
	DECLARE @p int;
	DECLARE @w int;
	
	-- Set parameters
	SET @Aantal_Free = -1;
	
	-- Check if product_number exists
	SELECT @p = COUNT(*) FROM Product WHERE product_number = @ProductNumber;
	IF @p = 0 SET @Message = 'Invalid product_number!';
	ELSE
    
		-- Check if warehouse_code exists
		SELECT @w = COUNT(*) FROM Warehouse WHERE warehouse_code = @WarehouseCode;
		IF @w = 0 SET @Message = 'Invalid warehouse_code!';
		ELSE
    
			-- Get inventory count
			SELECT @Aantal_Free = free FROM Inventory WHERE product_number = @ProductNumber AND warehouse_code = @WarehouseCode;
			IF @Aantal_Free = -1 SET @Message = '??';
			ELSE
			
				-- Get message for product in inventory
				IF @ProductNumber BETWEEN 1 AND 10 AND @Aantal_Free < 500 SET @Message = 'Add a new order for product_number ' + CAST(@ProductNumber AS varchar(5)) + '!';
				IF @ProductNumber BETWEEN 1 AND 10 AND @Aantal_Free >= 500 SET @Message = 'Currently free products: ' + CAST(@ProductNumber AS varchar(5));
				IF @ProductNumber BETWEEN 11 AND 21 AND @Aantal_Free < 750 SET @Message = 'Add a new order for product_number ' + CAST(@ProductNumber AS varchar(5));
				IF @ProductNumber BETWEEN 11 AND 21 AND @Aantal_Free >= 750 SET @Message = 'Currently free products: ' + CAST(@ProductNumber AS varchar(5));
				ELSE
				
					-- Product not in inventory
					SET @Message = 'Product_number ' + CAST(@ProductNumber AS varchar(5)) + ' has not been added to the inventory!';
	
	-- Print message		
	PRINT @Message;
	
END

I have a class 'Inventory' which has the following columns with the following data (only 1 test tuple!!)
- product_number: 11
- warehouse_code: 1
- physical: 600
- free: 450

When I execute my procedure with the data shown below, I always get the last message "Product_number 2 has not been added to the inventory"
- @ProductNumber = 2
- @WarehouseCode = 1

This is strange, because product_number 2 doesn't exists. So the PRINT @Message should return "Invalid product_number!"

What am I doing wrong?

Thanks in advance

!

Regards,
Tim

TK

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-05 : 10:32:12

The problem is in the statements where you are checking whether the product/warehouse code exists. For example, change this:


SELECT @p = COUNT(*) FROM Product WHERE product_number = @ProductNumber;


if exists (select * from Product WHERE product_number = @ProductNumber)
   set @p = 1
else
   set @p = 0;

The way you have it, @p will be null, and null is not equal to zero.

You will need to make a similar change in the place where you are comparing the @WarehouseCode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources