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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.