You are viewing limited content. For full access, please sign in.

Question

Question

Can you prohibit a stored procedure from running until a condition is met?

asked on January 28, 2014 Show version history

I have a stored procedure that auto-increments a Voucher Number.  I know by default the stored procedure is ran as soon as the form loads.  Is it possible to delay or prohibit that stored procedure from running until a condition is met.  For example, when the user filling out the form selects a radio button.

 

0 0

Answer

SELECTED ANSWER
replied on February 3, 2014

Place a condition in the stored procedure that says if the value is blank that you don't increment the value.

1 0

Replies

replied on January 28, 2014 Show version history

Yes, stored procedures use the same code as table lookup; so if the stored procedure takes a param it will monitor the field for changes before triggering.  So alter your procedure to accept a parameter like this:

 

Alter PROCEDURE [dbo].[sp_increment] 
    @notused varchar(50)  --added line
AS
BEGIN

 

Now add a new field to your form and assign it to 'notused'.  So now the SP will not be called until there is a change in the field.  

 

But that is not exactly what you asked for because you said you wanted to use a radio button not a input box to trigger the lookup.  Well lookup doesn't support changes in radio buttons to trigger a lookup out of the box.  You need to trigger a change event on the field. But this can be fixed with a little bit of JavaScript.

 

I added a class to my radio field called 'myradio' and when its clicked it fills the value into my 'notused' field which is linked to the lookup and triggers a change.

$(document).ready(function() {
  $('.myradio input').on('click', function() {
     $('.notused input').val($(this).val()).trigger('change');
  });
});

I then hide the 'notused' field with a a rule.

 

 

 

 

 

2 0
replied on January 30, 2014

Thank you for the quick response! I've configured my form and lookup to what you had suggested, however I must have done something wrong.  Now when my form loads, it still shows the stored procedure ran with and incremented the number, and further increments that number when I select the radio button.

0 0
SELECTED ANSWER
replied on February 3, 2014

Place a condition in the stored procedure that says if the value is blank that you don't increment the value.

1 0
replied on February 11, 2014

Rock on its working now.  Thanks again!

0 0
replied on April 10, 2014

I am trying to modify my stored procedure as suggested with:

Alter PROCEDURE [dbo].[sp_increment] 
    @notused varchar(50)  //added line
AS
BEGIN

 

I am getting an Incorrect syntax near '/' when I try to execute.  Did I miss something?

0 0
replied on April 11, 2014

Sorry,  I fixed the answer above to '--' instead of '//'.  The idea was to point out the new line I added and used the wrong syntax to comment out code in sql.smiley

0 0
replied on November 11, 2014

I'm not that versed in writing stored procedures.  Could someone post an example of a stored procedure that will only run if a particular value is not blank?

Thanks,

2 0
replied on January 21, 2016

Sorry to restart an old post but I am with Jen.  It would be good to have an example of the stored procedure to only run when a value is not blank...

I have read a few posts on the forum but cannot patch it all together...

So if I have two fields q1 and q2, what would be the stored procedure to fill q2 when q1 is filled in;

 

My stored procedure looks like;

USE [DynamicTable]
GO
/****** Object:  StoredProcedure [dbo].[GetPOUniqueNumber]    Script Date: 22/01/2016 10:31:40 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetPOUniqueNumber]
@q1 varchar(50)
AS
IF @q1 IS NOT NULL 
BEGIN

SET NOCOUNT ON;

INSERT INTO [dbo].[POUniqueNumbers]

([NewNo])

VALUES

(1)

SELECT SCOPE_IDENTITY() AS POUniqueNumber

END

Lookup Rule Snapshot

 

The number fills on form load only.  So obviously I have something wrong in the Stored Procedure..... can anyone help please? 

 

Cheers

Ross

 

0 0
replied on January 28, 2014

Thank you for the quick response! I've configured my form and lookup to what you had suggested, however I must have done something wrong.  Now when my form loads, it still shows the stored procedure ran with and incremented the number, and further increments that number when I select the radio button.

You are not allowed to follow up in this post.

Sign in to reply to this post.