NULL INSERT STORED PROCEDURE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

NULL INSERT STORED PROCEDURE

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2013-11-28 : 15:59:41

I try to create stored procedure to over come insert NULL in the table,
below is my stored procedure i got error on running this pls guide

CREATE PROCEDURE [dbo].[DEATHDATAINFO]
(

@FirstName [nvarchar](24) NULL,
@MiddleName [nvarchar](24) NULL,
@LastName [nvarchar](24) NULL,
@Title [nvarchar](4) NULL,
@BirthDate [datetime] NULL,
@DeathDate [datetime] NULL,
@FuneralDate [datetime] NULL,
@FuneralTime [nvarchar](10) NULL,
@FuneralPhone [nvarchar](15) NULL,
@FuneralContact [nvarchar](50) NULL,
@ObitText [nvarchar](max) NULL,
@ObitPhoto [nvarchar](80) NULL,
@Note [nvarchar](250) NULL,
@DateCreated [datetime] NULL,
@BurialTime [nvarchar](10) NULL,
@FuneralType [nvarchar](10) NULL,
@BurialType [nvarchar](10) NULL,
@VisitationLocation [nvarchar](80) NULL,
@VisitationDate [datetime] NULL,
@VisitationTime [nvarchar](50) NULL,
@DonationPhone [nvarchar](15) NULL,
@FuneralWebSite [nvarchar](50) NULL,
@BurialSiteWebSite [nvarchar](50) NULL,
@VisitationWebsite [nvarchar](50) NULL,
@DisplayOnWebsite [nvarchar](1) NULL,
@fid [int] NULL,
@bid [int] NULL,
@did [int] NULL,
@service [varchar](1) NULL
)
AS
BEGIN

SET NOCOUNT ON

IF ISNULL(@fid,'') = ''
SELECT @fid = NULL

IF ISNULL(@bid,'') = ''
SELECT @bid = NULL

IF ISNULL(@did,'') = ''
SELECT @did = NULL

INSERT INTO deathdata
(
FirstName, MiddleName, LastName,
Title, BirthDate, DeathDate,
FuneralDate, FuneralTime, FuneralPhone,
FuneralContact, ObitText, ObitPhoto,
Note, DateCreated, BurialTime,
FuneralType, BurialType, VisitationLocation,
VisitationDate, VisitationTime,DonationPhone,
FuneralWebSite,BurialSiteWebSite,DisplayOnWebsite,
fid,bid,did,servicesv
)
VALUES
(
@FirstName, @MiddleName, @LastName,@Title,
@lblbirthdate, @lbldeathdate, @lblfuneraldate,@FuneralTime,
@FuneralPhone, @funeralcontact,@newobitcontent,@ObitPhoto,
@newnotecontent,@DateTime,@BurialTime,@lblfuneraltype,
@lblburialtype,@visitationlocation,@lblvisitationdate,
@VisitationTime,@DonationPhone, @FuneralWebsite,
@BurialWebsite,@webinclude,@funerilid,@hdnburialid,@hdndonid,@servicetype

)

SET NOCOUNT OFF

END

Desikankannan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-28 : 19:35:54

quote:
I try to create stored procedure to over come insert NULL in the table,
below is my stored procedure i got error on running this pls guide

What do you want actually ? Insert NULL to the table or don't want to insert NULL to the table ?
What is the error message ?

your @fid is an integer type.

If you pass in blank space to it, it will be implicitly convert blank space to 0. Try this


declare @fid int
select @fid = ''
select @fid

with your @fid declare as integer, the following does not make sense


IF ISNULL(@fid,'') = ''
SELECT @fid = NULL

it should probably be


IF @fid IS NULL SELECT @fid = <some value>

or

SELECT @fid = ISNULL( @fid, <some value> )

KH
[spoiler]Time is always against us[/spoiler]

desikankannan
Posting Yak Master

152 Posts

Posted - 2013-11-29 : 00:17:57

Hi,

i want insert NULL value to table , please help me

quote:
Originally posted by khtan

quote:
I try to create stored procedure to over come insert NULL in the table,
below is my stored procedure i got error on running this pls guide

What do you want actually ? Insert NULL to the table or don't want to insert NULL to the table ?
What is the error message ?

your @fid is an integer type.

If you pass in blank space to it, it will be implicitly convert blank space to 0. Try this

declare @fid int
select @fid = ''
select @fid

with your @fid declare as integer, the following does not make sense

IF ISNULL(@fid,'') = ''
SELECT @fid = NULL

it should probably be

IF @fid IS NULL SELECT @fid = <some value>

or

SELECT @fid = ISNULL( @fid, <some value> )

KH
[spoiler]Time is always against us[/spoiler]

Desikankannan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-29 : 02:19:52

you want to insert NULL when you pass 0 to @fid ?


select @fid = NULLIF(@fid, 0)

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2013-11-29 : 03:33:27

We get around this by passing @fid as a string, checking if it is blank string, and if so converting it to NULL, otherwise converting it to an INT.

(Otherwise you cannot pass a ZERO value to the Sproc !!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources