ASP (not .NET): sending null as parameter to stored procedure in query string?

Question

I have the following code on an ASP page:

<%
    QueryToJSON(conn, "execute WebGetEmployeesPlanned'"
    +Request.QueryString("customercode")+"', 
    '"+Request.QueryString("lang")+"', 
    '"+Request.QueryString("date")+"'").flush
%>

There is no other code on this page except some includes, as I call this page with ajax from another page, using $.getJSON()

The problem is that I don't know how I can send NULL as one of the parameters to the SP.

Sometimes Request.QueryString("customercode") will be empty. I need to know how I can put that in the code above to make the SP see this parameter as NULL.

I have access to the SP code so I can change something on that side to convert an empty string to null.

Thanks in advance.

Answer 1

You're on the right track. Query string parameters are just strings. So you have to choose a string representative for NULL which won't conflict with any of the actual string values you will be sending. You could use empty string, "null", or "Thomas's special null flag"; it doesn't matter. There is no "official" representation of NULL in a query string parameter.

Update: no, I don't think the stored procedure is the place to handle this translation. The reason is that the fact that you are converting your string representation of the null in the query string parameter to a "real" null is something that you have to do because of the limitations of what you can put into a query string parameter. Doing this conversion in the stored procedure would make the procedure, to some degree, "aware" of the query string, and that doesn't feel right. Instead, I would do it in the aspx markup, which is expected to be web-aware. Here's a source example (untested, so fix my syntax errors or, better, change to string.Format() or a parameterized query...

Change:

QueryToJSON(conn, "execute WebGetEmployeesPlanned'"
+Request.QueryString("customercode")+"', 
'"+Request.QueryString("lang")+"', 
'"+Request.QueryString("date")+"'").flush

to:

QueryToJSON(conn, "execute WebGetEmployeesPlanned "
    + (string.IsNullOrEmpty(Request.QueryString("customercode")) ?
          "null, '"
          :
          "'" + Request.QueryString("customercode") + "','")
    +Request.QueryString("customercode")+"', 
    '"+Request.QueryString("lang")+"', 
    '"+Request.QueryString("date")+"'").flush