MS Access FE SQL Server BE Create Stored Procedure

I have an Access database with a Main form which user inputs a product_nbr into txtProduct_Nbr.  I need to call a stored proc to determine if the product_nbr has been checked out

I have a Product_nbr_checkout table with the following fields:  product_nbr, userid, checkout_date, checkin_date

I need to write a stored proc to do the following:

If @product_nbr IN (Select  product_nbr from Product_nbr_checkout) and if checkin_date is null then send message to user in Access:  "Product is currently checked out"

Else If @product_nbr IN (Select  product_nbr from Product_nbr_checkout) and if checkin_date is not null then
   insert into product_nbr_checkout product_nbr, userid, currentdate

Else If @product_nbr NOT IN (Select  product_nbr from Product_nbr_checkout) and if checkin_date is null then
     insert into product_nbr_checkout product_nbr, userid, currentdate

I need to know how to write the stored procedure .  

Thanks much for your help!
deer777Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Do you want to create the Stored Procedure in SQL Server ? if yes you can do like below-

CREATE PROC CheckingProduct
(
	 @product_nbr INT
	,@userID INT
)
AS
BEGIN

	DECLARE @TextPrint AS VARCHAR(250) = ''
	IF EXISTS ( Select  product_nbr from Product_nbr_checkout WHERE product_nbr = @product_nbr AND checkin_date is null )
	BEGIN			
			SET @TextPrint = 'Product is currently checked out'
	END
	ELSE IF EXISTS ( Select  product_nbr from Product_nbr_checkout WHERE product_nbr = @product_nbr AND checkin_date is NOT null )
	BEGIN
			INSERT INTO product_nbr_checkout(product_nbr, userid, currentdate)
			VALUES (@product_nbr,@userID,GETDATE()) 
			SET @TextPrint = 'Record Inserted in product_nbr_checkout'
	END
	ELSE IF NOT EXISTS ( Select  product_nbr from Product_nbr_checkout WHERE product_nbr = @product_nbr AND checkin_date is null )
	BEGIN
			INSERT INTO product_nbr_checkout(product_nbr, userid, currentdate)
			VALUES (@product_nbr,@userID,GETDATE()) 
			SET @TextPrint = 'Record Inserted in product_nbr_checkout'
	END
	SELECT @TextPrint
END

Open in new window


See last 2 conditions are same so you can club them.
0
 
ste5anSenior DeveloperCommented:
Caveat: You have a model issue. The checkout table must have a mandatory date column. It cannot be NULL.
0
 
deer777Author Commented:
Looks good.  Now I need to call this proc from Access.  Can you tell me how you would do this.  It would be call from a textbox after update vba code.
0
 
deer777Author Commented:
Was able to run this stored procedure from access via sql pass through however I am now getting an err stating that my stored procedure expects a parameter @product_nbr which was not supplied.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm pretty sure you will need to create the SQL for the query in VBA code, and set the parameter on the fly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.