Inserting "null" (literally) in to a stored procedure parameter.

Question

I'm trying to insert the word "Null" (literally) in to a parameter for a stored procedure. For some reason SqlServer seems to think I mean NULL and not "Null". If I do a check for

IF @LastName IS NULL // Test: Do stuff

Then it bypasses that because the parameter isn't null.

But when I do:

INSERT INTO Person (<params>) VALUES (<stuff here>, @LastName, <more stuff here>); // LastName is 'Null'

It bombs out saying that LastName doesn't accept nulls. I would seriously hate to have this last name, but someone does... and it's bombing the application. We're using SubSonic 2.0 (yeah, it's fairly old but upgrading is painful) as our DAL and stepping through it, I see it does create the parameters properly (for what I can tell).

I've tried creating a temp table to see if I could replicate it manually but it seems to work just fine. Here is the example I create:

DECLARE @myval VARCHAR(50)
SET @myval = 'Null'
CREATE TABLE #mytable( name VARCHAR(50))
INSERT INTO #mytable VALUES (@myval)
SELECT * FROM #mytable
DROP table #mytable

Any thoughts on how I can fix this?

edit: The method the LastName is set is this -- myPerson.LastName = textBoxLastName.Text;

edit: Upon using SqlServer Profiler, I see that it IS sending null. Two things that's weird, I wonder why my "IF @lastName IS NULL" isn't firing off... but now I have to look at SubSonic further to see if something is changing last minute I wasn't aware of. I'll investigate this further and report back.

edit: The only constraint is a default value of ''.

edit: Ok, I've confirmed it IS SubSonic trying to play smart.

 if (param.ParameterValue == null || Utility.IsMatch(param.ParameterValue.ToString(), "null"))

Good idea for using the Profiler, I entirely forgot about that.

final edit: It's worth noting, fur future reference, that SubSonic replaces this at the last minute in the data provider (in my case SqlDataProvider). In The DataService.cs where it calls Execute Scalar, the parameters have not been adjusted yet. They get adjusted when it runs the ExecuteScalar for the DataProvider, which is an abstract class (so Postgre and Oracle will each have their own bits of code). Specifically, the SubSonic.SqlDataProvider.AddParams is what bit me.

Answer 1

Please try this:

Oops...I totally misunderstood the question. I think there should be some constraints set in the database column. Please try to write the insert query directly in DB and see if it bombs out.

I tried this and it bombs out as expected:

DECLARE @myval VARCHAR(50)
SET @myval = 'null'
CREATE TABLE #mytable( name VARCHAR(50) NULL CHECK (name <> 'NULL'))
INSERT INTO #mytable VALUES (@myval)
SELECT * FROM #mytable WHERE name is not null
DROP table #mytable

HTH

Answer 2

It's worth noting, fur future reference, that SubSonic replaces this at the last minute in the data provider (in my case SqlDataProvider). In The DataService.cs where it calls Execute Scalar, the parameters have not been adjusted yet. They get adjusted when it runs the ExecuteScalar for the DataProvider, which is an abstract class (so Postgre and Oracle will each have their own bits of code). Specifically, the SubSonic.SqlDataProvider.AddParams is what bit me.