July 6, 2010 at 1:29 am
Dear Sir,
I have created a store procedure which insert data into a table. but before inserting data we check the inserted data already exists in the table.
in this case i have declared a varchar(10) variable. and wanted to define null value with select command and the check if the variable contain null value then I confirm there is no record in the table and trying to insert data into the table
[Code]
Creat sp_Insertdata_i
As
Set ansi_nulls off
set quoted_identifer off
set nocount off
declare @slpno As Varchar(10)
--checking existing data already exists or not if not the initilizing
--null value in @slpno variable
set @slpno=(Select slipno from tranfile where procid='P0001')
If @slpno is null -- here checking null value if true the inserting data
insert into tranfile(slipno,procid) values('S000000001','P0001')
else
--Updating table with the record
Return
[Code]
July 6, 2010 at 2:28 am
Hi ,
Can you expand on what your problem is ? Is it inserting when it shouldn't ? or updating when it shouldn't ?
There doesent seem to be anything related to ansi null in your code , you are testing will 'is null' which is good.
AnsiNull is related to testing the (in)equality of a NULL value, consider this code
set ansi_nulls on
declare @nullv varchar(10)
if(@nullv <>1) begin
select 'not equal - ansinulls on'
end
if(@nullv =1) begin
select 'equal value - ansinulls on'
end
if(@nullv =NULL) begin
select 'equal null - ansinulls on'
end
go
set ansi_nulls off
declare @nullv varchar(10)
if(@nullv <>1) begin
select 'not equal - ansinulls off'
end
if(@nullv =1) begin
select 'equal - ansinulls off'
end
if(@nullv =NULL) begin
select 'equal null - ansinulls off'
end
July 6, 2010 at 2:58 am
1. A procedure has the settings for QUOTED_IDENTIFIER and ANSI_NULLS from when it is created.
(ie They need to be defined before creating the procedure.) I see no need for setting these OFF.
2. User procedures should not start with SP_ as every call will look in the master DB first and slow things down.
Try something like:
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.UpsertTranfile
@slipno varchar(10)
,@procid varchar(5)
AS
SET NOCOUNT ON
BEGIN TRY
INSERT INTO dbo.tranfile(slipno, procid)
SELECT @slipno, @procid
WHERE NOT EXISTS
(
SELECT *
-- increase isolation for this check
FROM dbo.tranfile WITH (UPDLOCK, SERIALIZABLE)
WHERE procid = @procid
)
IF @@ROWCOUNT = 0 -- no insert
UPDATE dbo.tranfile
SET slipno = @slipno
WHERE procid = @procid
END TRY
BEGIN CATCH
RAISERROR('Upsert of tranfile failed', 16, 1)
END CATCH
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply