mardi 26 mars 2019

i need to replace possible NULL value for insertion of record into a database with a new value

i have written a stored procedure which should be doing one thing - checking for existence of keyfield value in some reference table for a specific textfield value and if it doesn't exist it should be inserted and then used in further operator. but for unknown reasons it doesn't work, specifically operators inside IF bodies do not work

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE spAddBookEx 
@title nvarchar(50), @author nvarchar(50), @genre nvarchar(50), @cover 
    nvarchar(50), @pubYear char(4)
AS
BEGIN
SET NOCOUNT ON;

declare @a uniqueidentifier, @g uniqueidentifier, @c uniqueidentifier;
set @a = (select uid from authors where name=@author);
if @a=NULL
begin
    set @a=newid();
    insert into authors values(@a, @author);
end
set @g = (select uid from genres where name=@genre);
if @g=NULL
begin
    set @g=newid();
    insert into genres values(@g, @genre);
end
set @c = (select uid from covers where name=@cover);
if @c=NULL
begin
    set @c=newid();
    insert into covers values(@c, @cover);
end

insert into books values(newid(), @title, @a, @g, @c, @pubYear);
END
GO

Aucun commentaire:

Enregistrer un commentaire