Empty/Null values in SPs

[CJM]

I frequently come across a small problem with my stored procedures; there
are plenty of way around it, but I'm figuring that maybe my approach is
subtly wrong.

My typical code to call an Stored Proc in ASP is as follows

sSQL = "Exec MySP 'xxx', 111, 'yyy', 222"
oConn.Execute sSQL

Usually the sSQL string is built up from several variables, e.g.

sSQL = "Exec MySP '" & sXXX & "', " & iOnes & "', '" & sYYY & "', " & iTwos

Sample SP:
Create Proc MySP
@XXX varchar(10) = null,
@Ones int = null,
@YYY varchar(10) == null,
@Twos int = null
As
etc....


If all arguments are there, everything is fine. If one of the strings is
missing, it is not a problem either since '' is passed through.

However, if one of the non-string values is missing, and error is raised
(Incorrect syntax near ','):
sSQL = "Exec MySP 'xxx', , 'yyy', 222"

I have a default value specified in the SP, so why doesnt it like this?

The two most obvious solutions are to enclose non-string values in single
quotes(1) , which strikes me as being slightly sacreligious(!), or by
detecting where a value is null and including the null keyword(2):

1) sSQL = "Exec MySP 'xxx', '', 'yyy', 222"
2) sSQL = "Exec MySP 'xxx', null, 'yyy', 222"


Am I missing something obvious or is this just the way it is?

Thanks

Chris

 

[CJM]

Apologies:
Although not entirely OT, this was supposed to be post to
microsoft.public.sqlserver.progreamming.

If anyone from this NG is interested in this thread, I would suggest you
follow-up in m.p.s.p.

Chris