October 17, 2008 at 11:24 am
Hi all,
I have the following basic t-sql statement:
INSERT INTO PROVIDERS (LAST_NAME, FIRST_NAME, MI, DOB, SS_NUMBER, EMAIL, GENDER)
VALUES (@LastName, @FirstName, @MI, @DOB, @SS, @Email, @Gender)
The variable values are external but must be included. The issue I'm having is some values can be null. These null values are not known until runtime. How can I use the statement without having to resort to writing dynamic sql to account when/if null values come in?
Thanks,
Strick
October 17, 2008 at 11:45 am
Hi, it depends on what you want to do, insert another value to replace the Null value, or simply not insert. Here is an example if you want to insert another value:
INSERT INTO PROVIDERS (LAST_NAME, FIRST_NAME, MI, DOB, SS_NUMBER, EMAIL, GENDER)
VALUES (
case
when @LastName is null then ''
else @LastName
end,
case
when @FirstName is null then ''
else @FirstName
end,
case
when @MI is null then ''
else @MI
end,
case
when @DOB is null then ''
else @DOB
end,
case
when @SS is null then ''
else @SS
end,
case
when @Email is null then ''
else @Email
end,
case
when @Gender is null then ''
else @Gender
end
Or you can simply evaluate the values before you insert them, with ifs validations.
Tell me if this helps,
Sincerely,
Cheers,
J-F
October 17, 2008 at 11:47 am
If your table allows nulls for those columns, the code you have written will work. If not, you can wrap the variables in an ISNULL to provide a default value. If neither of those help, are you getting an error of some kind when you try the insert?
Thanks,
Chad
October 17, 2008 at 11:54 am
Hi all thanks for your response. Unfortunately I can't insert '' into DB since null and '' are not the same thing. Destination would need to be null just like source.
Thanks,
Strick
October 17, 2008 at 12:00 pm
Is this INSERT statement inside a stored procedure? We typically make the parameters of the stored procedure optional when they can be NULL in the database, and then the calling application only passes in the parameters that it actually has values for.
October 17, 2008 at 12:03 pm
stricknyn (10/17/2008)
Hi all thanks for your response. Unfortunately I can't insert '' into DB since null and '' are not the same thing. Destination would need to be null just like source.Thanks,
Strick
Your first statement does not precludes the use of NULL.
* Noel
October 17, 2008 at 2:53 pm
Can you clarify your issue? If both the source and destination allow nulls, and the @variable is null, the code you have should work, right?
Here's a really short example:
CREATE TABLE PROVIDERS (Last_Name varchar(50) NULL) --Allow nulls in the table
DECLARE @Last_Name varchar(50)
SELECT @Last_Name --Value has not been assigned, so it is NULL
INSERT INTO PROVIDERS (Last_Name)
VALUES (@Last_Name)
SELECT * FROM PROVIDERS --returns one row with the NULL value
What kind of issue are you having that is making it not work? An error of some kind? Data in the table not correct?
Thanks,
Chad
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply