SQL Server optional variables

[jasonsalas]

I'm trying to allow a large form to be sent and processed by using a SQL Server stored procedure. I declared several variables for the SP, like so:

CREATE PROCEDURE sp_CastVote
@PERSON1 smallint,
@PERSON2 smallint,
@PERSON3 smallint,
...
@PERSONn smallint
AS
INSERT INTO tableName (f1,f2,f3...fn) VALUES (@PERSON1, @PERSON2,@PERSON3...@PERSONn)
GO


However, the fields aren't each required in the form, and when they pass empty values, I get a SQL error saying "expected a parameter." Does anyone know how I can get around this?

 

[mkuan]

How about changing the columns in the table (tableName)you're inserting into to "allow null", or switching the to-be inserted parameters to something like "0" before sending them off to stored proc.

 

[JuanitaC]

You can declare your parameters to be optional like this:

CREATE PROCEDURE sp_CastVote
@PERSON1 smallint=NULL,
@PERSON2 smallint=NULL,
@PERSON3 smallint=NULL,
...
@PERSONn smallint=NULL
AS
INSERT INTO tableName (f1,f2,f3...fn) VALUES (@PERSON1, @PERSON2,@PERSON3...@PERSONn)
GO

Then, when you call your stored proc from your ASP page, just make sure you set the empty variables to NULL before passing to the stored proc.

if Len(person1)=0 then
person1="NULL"
else
person1 = "'" & person1 & "'"
end if

....

SQL = "Exec sp_CastVote " & person1 & ", " etc.