Stored Procedure Inputs

[ryandoah]

Hi all,
I am using ASP/ADO and SQL7 on Win2000svr/IIS5. I have some pages that I am inputting data. Some of the fields on the page are not required data. I really don't feel like coding for all of the contingencies that could occur, in the page, nor do I want to have to right separate SP's for each of the contingencies. Can I use something like the IF EXISTS statement in the SP to determine whether a SP variable should be used or not?

Something like this:

Create Stored Procedure spSomeProc

@someVar1 varchar [50],
@somevar2 varchar [50],
If Exists @SomeVar varchar [50],
etc.

I've been told that using RDO, stored procedures will ignore any variables that aren't actually passed into the procedure, but I am using ADO, which doesn't allow such conveniencies.

Thanks,
ryandoah

 

[SQLSister]

If the field allows nulls, then set the variable default to null when you declare it in the stored procedure. Then just write one insert statement with all the variables. It will insert the value null for any which were not passed in.

Here's how to define a default value for an input variable:

CREATE              PROCEDURE  USP_SELECT_TestProcedure (@AirCode as nvarchar(4) = NULL) 

AS