Empty/Null values in SPs

C

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top