SE EXISTIR, ENTÃO SELECCIONE OUTRO INSERIR E SELECCIONE
Como se diz o seguinte no Microsoft SQL Server 2005:
IF EXISTS (SELECT * FROM Table WHERE FieldValue='') THEN
SELECT TableID FROM Table WHERE FieldValue=''
ELSE
INSERT INTO TABLE(FieldValue) VALUES('')
SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END IF
o que estou a tentar fazer é ver se já existe um valor de campo em branco, e se já existe um TableID, então introduza um valor de campo em branco e devolva a chave primária correspondente.
7 answers
Tem de o fazer na transacção para garantir que dois clientes simultâneos não insiram o mesmo valor de campo duas vezes:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
DECLARE @id AS INT
SELECT @id = tableId FROM table WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
INSERT INTO table (fieldValue) VALUES (@newValue)
SELECT @id = SCOPE_IDENTITY()
END
SELECT @id
COMMIT TRANSACTION
Também pode usar Bloqueio duplo-verificado para reduzir a sobrecarga de bloqueio
DECLARE @id AS INT
SELECT @id = tableID FROM table (NOLOCK) WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @id = tableID FROM table WHERE fieldValue=@newValue
IF @id IS NULL
BEGIN
INSERT INTO table (fieldValue) VALUES (@newValue)
SELECT @id = SCOPE_IDENTITY()
END
COMMIT TRANSACTION
END
SELECT @id
Quanto à razão pela qual o nível de isolamento serializável é necessário, quando você está dentro de uma transacção serializável, a primeira opção que atinge a tabela cria um intervalo de bloqueio que cobre o local onde o registo deve estar, para que mais ninguém possa inserir o mesmo registo até que isto a transacção termina.
Sem o nível de isolamento serializável, o nível de isolamento padrão (READMITED) não iria bloquear a tabela no tempo de leitura, então entre selecionar e atualizar, alguém ainda seria capaz de inserir. As transacções com o nível de isolamento recomendado não fazem com que a selecção se tranque. As transacções com leitura repetível bloqueiam o registo (se forem encontradas), mas não a lacuna.
IF EXISTS (SELECT 1 FROM Table WHERE FieldValue='')
BEGIN
SELECT TableID FROM Table WHERE FieldValue=''
END
ELSE
BEGIN
INSERT INTO TABLE(FieldValue) VALUES('')
SELECT SCOPE_IDENTITY() AS TableID
END
Veraqui para mais informação sobre se há mais
Nota: escrita sem uma instalação de servidor SQL à mão para verificar isto de novo, mas acho que está correcta
Também, eu mudei o bit existe para fazer selecionar 1 em vez de selecionar * como você não se importa com o que é devolvido dentro de UMA existe, desde que algo é Também mudei o bit SCOPE_ Identity () para devolver apenas a identidade, assumindo que o TableID é a coluna de identidade
Estavas perto.
IF EXISTS (SELECT * FROM Table WHERE FieldValue='')
SELECT TableID FROM Table WHERE FieldValue=''
ELSE
BEGIN
INSERT INTO TABLE (FieldValue) VALUES ('')
SELECT TableID FROM Table WHERE TableID=SCOPE_IDENTITY()
END
Só tens de mudar a estrutura do if...else..endif
um pouco:
if exists(select * from Table where FieldValue='') then begin
select TableID from Table where FieldValue=''
end else begin
insert into Table (FieldValue) values ('')
select TableID from Table where TableID = scope_identity()
end
Também podes fazer:
if not exists(select * from Table where FieldValue='') then begin
insert into Table (FieldValue) values ('')
end
select TableID from Table where FieldValue=''
Ou:
if exists(select * from Table where FieldValue='') then begin
select TableID from Table where FieldValue=''
end else begin
insert into Table (FieldValue) values ('')
select scope_identity() as TableID
end
INSERT
vai falhar. Não se preocupe: você só precisa garantir que a aplicação não veja/ignore o erro. Quando você diz "chave primária" você presumivelmente significa valor {[[2]}. Isso é tudo muito bem, mas você também precisa de uma restrição chave (por exemplo UNIQUE
) em sua chave natural.
Além disso, pergunto-me se o seu procedimento está a fazer demasiado. Considerar ter separado procedimentos para as acções "criar" e "ler", respectivamente.
DECLARE @t1 TABLE (
TableID int IDENTITY,
FieldValue varchar(20)
)
--<< No empty string
IF EXISTS (
SELECT *
FROM @t1
WHERE FieldValue = ''
) BEGIN
SELECT TableID
FROM @t1
WHERE FieldValue=''
END
ELSE BEGIN
INSERT INTO @t1 (FieldValue) VALUES ('')
SELECT SCOPE_IDENTITY() AS TableID
END
--<< A record with an empty string already exists
IF EXISTS (
SELECT *
FROM @t1
WHERE FieldValue = ''
) BEGIN
SELECT TableID
FROM @t1
WHERE FieldValue=''
END
ELSE BEGIN
INSERT INTO @t1 (FieldValue) VALUES ('')
SELECT SCOPE_IDENTITY() AS TableID
END
create schema tableName authorization dbo
go
IF OBJECT_ID ('tableName.put_fieldValue', 'P' ) IS NOT NULL
drop proc tableName.put_fieldValue
go
create proc tableName.put_fieldValue(@fieldValue int) as
declare @tableid int = 0
select @tableid = tableid from table where fieldValue=''
if @tableid = 0 begin
insert into table(fieldValue) values('')
select @tableid = scope_identity()
end
return @tableid
go
declare @tablid int = 0
exec @tableid = tableName.put_fieldValue('')