Script to execute stored procedure only if required field is not empty

[uncleroydee]

I'm creating a form to insert record(s) to a SQL database via a stored procedure and plan to allow the users to insert between 1 and 15 records in one submission. This creates a SQL problem for me.

I intend to write 15 seperate execute stored preocedure commands eg "exec SP_myprocedure1 1Param1, 1Param2, 1Param3... exec SP_myprocedure2 2Param1, 2Param2, 2Param3...". The problem is when SQL Server executes a stored procedure it expects to see a value for every parameter included in that stored procedure and fails with an error message when it encounters an empty parameter. Therefore, I need to write an "if" function that will execute the stored procedure only when a key parameter within the procedure contains a value and, will end without error when it encounters the first empty key parameter.

I am working on data validation for the insert form to ensure that all fields in a particular record are complete (reference posting "Required field validation function&quot but if anyone has any experience writing a script that will check for a value before executing a stored procedure it would be a great help.

Thanks for your time, I welcome all suggestions.

 

[Klae]

I don't think I understand correctly but here goes..

Say you have 3 fields requiring filling in.
have 3 booleans all initialised to FALSE.
have a validation function that checks the content of each field like:

if (document.field1.value != "&quot
{
boolField1 = TRUE;
}

At the end of validating all 3 fields, have..

If (boolfield1 && boolField2 && boolField3)
{
go to SQL stored procedures
}

Do you understand what I mean? I'm not sure if i've made that clear..
Klae{/color]

You're only as good as your last answer!

 

[uncleroydee]

Actually, it should go more like this.

There are 15 instances of the same stored procedure each inserting one record, each record has one required field which we'll assume is parameter1. Since users may not have 15 records to insert at once, I need to code something to accomplish the following:

Step 1. If the first parameter1 contains value then execute the first stored procedure and proceed to step 2, else end.
Step 2. If the second parameter1 contains value then execute the second stored procedure and proceed to step 3, else end.
Step 3. If the third parameter1 contains value then execute the third stored procedure and proceed to step 4, else end.
.
.
.
through step 15.

Here's the first two iterations of the execute stored procedure statement with my crude attempt to write the if..then..else statement. The value NSN* is the required value.

IF NSN1 <> &quot;&quot; THEN
&quot;exec SP_SrgDB_Insrt_NSN&quot; &_
&quot;'&quot; & NSN1 & &quot;', &quot; & _
&quot;'&quot; & S_NBR_Agcy & &quot;', &quot; & _
&quot;'&quot; & S_NBR_YR & &quot;', &quot; & _
&quot;'&quot; & S_NBR_RFQ_TYPE & &quot;', &quot; & _
&quot;'&quot; & S_NBR_RFQ_ISSUE_ORDER & &quot;', &quot; & _
&quot;'&quot; & C_NBR_Agcy & &quot;', &quot; & _
&quot;'&quot; & C_NBR_YR & &quot;', &quot; & _
&quot;'&quot; & C_NBR_RFQ_TYPE & &quot;', &quot; & _
&quot;'&quot; & C_NBR_RFQ_ISSUE_ORDER & &quot;', &quot; & _
&quot;'&quot; & ITEMDESCR1 & &quot;', &quot; &_
&quot;'&quot; & SD1_1 & &quot;', &quot; &_
&quot;'&quot; & SD1_2 & &quot;', &quot; &_
&quot;'&quot; & SD1_3 & &quot;', &quot; &_
&quot;'&quot; & SD1_4 & &quot;', &quot; &_
&quot;'&quot; & SD1_5 & &quot;', &quot; &_
&quot;'&quot; & SD1_6 & &quot;', &quot; &_
&quot;'&quot; & EXPLANATION1 & &quot;'&quot;
ELSE END IF

IF NSN2 <> &quot;&quot; THEN
&quot;exec SP_SrgDB_Insrt_NSN&quot; &_
&quot;'&quot; & NSN2 & &quot;', &quot; & _
&quot;'&quot; & S_NBR_Agcy & &quot;', &quot; & _
&quot;'&quot; & S_NBR_YR & &quot;', &quot; & _
&quot;'&quot; & S_NBR_RFQ_TYPE & &quot;', &quot; & _
&quot;'&quot; & S_NBR_RFQ_ISSUE_ORDER & &quot;', &quot; & _
&quot;'&quot; & C_NBR_Agcy & &quot;', &quot; & _
&quot;'&quot; & C_NBR_YR & &quot;', &quot; & _
&quot;'&quot; & C_NBR_RFQ_TYPE & &quot;', &quot; & _
&quot;'&quot; & C_NBR_RFQ_ISSUE_ORDER & &quot;', &quot; & _
&quot;'&quot; & ITEMDESCR2 & &quot;', &quot; &_
&quot;'&quot; & SD2_1 & &quot;', &quot; &_
&quot;'&quot; & SD2_2 & &quot;', &quot; &_
&quot;'&quot; & SD2_3 & &quot;', &quot; &_
&quot;'&quot; & SD2_4 & &quot;', &quot; &_
&quot;'&quot; & SD2_5 & &quot;', &quot; &_
&quot;'&quot; & SD2_6 & &quot;', &quot; &_
&quot;'&quot; & EXPLANATION2 & &quot;'&quot;
ELSE END IF

Thanks for the response.

Roy

 

[uncleroydee]

I finally got it through my thick cranium that performance would not be an issue, since the user was submitting something anyway and bandwidth requirements for this operation are miniscule. So I put an If...Else in my SQL stored procedure so that it will execute only when the required field is not empty.

Thanks for your consideration.

Roy