|
Home > Archive > IIS ASP > May 2004 > Empty/Null values in SPs
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Empty/Null values in SPs
|
|
|
| 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
| |
|
| 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
|
|
|
|
|