declare @sql varchar(max)='create proc [dbo].[up_i_u_<TableName>] @<TableName>ID smallint = null, @<TableName>Name varchar(max)=null
as';
set @sql = replace(@sql,'<TableName>','Country');
print (@sql);
--exec(@sql);
Динамический SQL
У меня есть БД. В ней есть несколько таблиц со словарем.
На таблицу codeCity
CityID (PK, identity, smallint, not null)
CityName (varchar(30), not null)
построила вот такую процедуру.
ALTER PROCEDURE [dbo].[up_i_u_codeCity]
(
@CityID smallint=null ,
@CityName varchar(max)=null)
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.codeCity
WHERE LTRIM(RTRIM(CityName)) = LTRIM(RTRIM(@CityName)))
AND LTRIM(RTRIM(@CityName)) IS NOT NULL OR LEN(LTRIM(RTRIM(@CityName)))>20
BEGIN
IF LEN(LTRIM(RTRIM(@CityName)))>20
SELECT 'Length of city '+@CityName+ ' to long, not inserted.' as Not_allowad_length
ELSE
SELECT 'Duplicate values not allowed' as Duplication
END
ELSE
IF EXISTS (SELECT * FROM dbo.codeCity
WHERE CityID = @CityID ) --and isnumeric(@CityID)
AND LTRIM(RTRIM(@CityName)) IS NOT NULL
BEGIN
UPDATE dbo.codeCity
SET
CityName = @CityName
WHERE CityID = @CityID
SELECT 'City updated' as Successeful_update
END
ELSE
IF ISNULL(@CityName,'') = ''
BEGIN
SELECT 'No name introduced.' as Value_is_NULL
END
ELSE
BEGIN
INSERT INTO dbo.codeCity VALUES (LTRIM(RTRIM(@CityName)))
select 'City name inserted'
end
--exec up_i_u_codeCity
--exec up_i_u_codeCity '',' r jes2a '
--exec up_i_u_codeCity '9','jerjdkoghkusa'
--exec up_i_u_codeCity null,' 23fgh '
Как построить динамическую процедуру, что бы она проверила мою БД и на все однотипные таблицы (словари) построила такие же процедуры.
Например, на codeProfession, codeEducation
На таблицу codeCity
CityID (PK, identity, smallint, not null)
CityName (varchar(30), not null)
построила вот такую процедуру.
ALTER PROCEDURE [dbo].[up_i_u_codeCity]
(
@CityID smallint=null ,
@CityName varchar(max)=null)
AS
SET NOCOUNT ON
IF EXISTS (SELECT * FROM dbo.codeCity
WHERE LTRIM(RTRIM(CityName)) = LTRIM(RTRIM(@CityName)))
AND LTRIM(RTRIM(@CityName)) IS NOT NULL OR LEN(LTRIM(RTRIM(@CityName)))>20
BEGIN
IF LEN(LTRIM(RTRIM(@CityName)))>20
SELECT 'Length of city '+@CityName+ ' to long, not inserted.' as Not_allowad_length
ELSE
SELECT 'Duplicate values not allowed' as Duplication
END
ELSE
IF EXISTS (SELECT * FROM dbo.codeCity
WHERE CityID = @CityID ) --and isnumeric(@CityID)
AND LTRIM(RTRIM(@CityName)) IS NOT NULL
BEGIN
UPDATE dbo.codeCity
SET
CityName = @CityName
WHERE CityID = @CityID
SELECT 'City updated' as Successeful_update
END
ELSE
IF ISNULL(@CityName,'') = ''
BEGIN
SELECT 'No name introduced.' as Value_is_NULL
END
ELSE
BEGIN
INSERT INTO dbo.codeCity VALUES (LTRIM(RTRIM(@CityName)))
select 'City name inserted'
end
--exec up_i_u_codeCity
--exec up_i_u_codeCity '',' r jes2a '
--exec up_i_u_codeCity '9','jerjdkoghkusa'
--exec up_i_u_codeCity null,' 23fgh '
Как построить динамическую процедуру, что бы она проверила мою БД и на все однотипные таблицы (словари) построила такие же процедуры.
Например, на codeProfession, codeEducation
а смысл этого всего? а то сдается что всё не так должно быть...
Если вы знаете правила именования в соответствии с которыми у вас образуются в процедуре названия переменных, таблиц и т.д. то можете сделать типа шаблона с place holder-ами, на мето которых потом подставить реальные значения, типа такого
Только присмотритесь сперва к вашей процедуре.
Самая первая проверка, почему там идет проверка на существование и на не нулл, а потом OR и проверка на длину? Т.е. если подойдет по длине, то можно даже вставить дубликат.
Далее вы при поиске делаете "WHERE LTRIM(RTRIM(CityName)) = ", зачем это? Чтобы убить возможность использования индекса, который можно было бы создать на поле для ускорения поиска по индексу? Не проще ли гарантировать удаление конечных и начальных пробелов при вставке (вы кстати это делаете на инсерте) просто добавьте еще и на апдейте.
Когда вы проверяете на not null, зачем там триммите? Если там нулл - все сработает и так, если нет, тоже сработает, без тримов.
Вообще с LTRIM(RTRIM - перебор. Например, функция len уже сама заботится об этом, сравните:
select LEN( LTRIM(RTRIM(' aaa ')) ), LEN(LTRIM(RTRIM('aaa')))
Ну и самое главное, надеюсь, эта проверка просто добавляет лишний уровень контроля, а на самом деле, на таблице codeCity, на поле имя, есть уникальный констрэйнт или индекс, который запрещает дубли на уровне хранения. Иначе, при уровне изоляции по-умолчанию, даже при соблюдении всех условий процедуры, возможны дубли.
Ну и если вам поможет, то мой вариант процедуры был бы примерно такой:
Код:
Самая первая проверка, почему там идет проверка на существование и на не нулл, а потом OR и проверка на длину? Т.е. если подойдет по длине, то можно даже вставить дубликат.
Далее вы при поиске делаете "WHERE LTRIM(RTRIM(CityName)) = ", зачем это? Чтобы убить возможность использования индекса, который можно было бы создать на поле для ускорения поиска по индексу? Не проще ли гарантировать удаление конечных и начальных пробелов при вставке (вы кстати это делаете на инсерте) просто добавьте еще и на апдейте.
Когда вы проверяете на not null, зачем там триммите? Если там нулл - все сработает и так, если нет, тоже сработает, без тримов.
Вообще с LTRIM(RTRIM - перебор. Например, функция len уже сама заботится об этом, сравните:
select LEN( LTRIM(RTRIM(' aaa ')) ), LEN(LTRIM(RTRIM('aaa')))
Ну и самое главное, надеюсь, эта проверка просто добавляет лишний уровень контроля, а на самом деле, на таблице codeCity, на поле имя, есть уникальный констрэйнт или индекс, который запрещает дубли на уровне хранения. Иначе, при уровне изоляции по-умолчанию, даже при соблюдении всех условий процедуры, возможны дубли.
Ну и если вам поможет, то мой вариант процедуры был бы примерно такой:
Код:
--------------------------
-- Create table
create table dbo.City(CityID smallint identity primary key, CityName varchar(20) not null);
create unique nonclustered index ix_CityName on dbo.City(CityName);
go
--------------------------
-- Create proc
create proc [dbo].[City_save]
@CityID smallint=null output,
@CityName varchar(max)=null
AS
set nocount on
--validate params
if @CityName is null begin
raiserror('City name is not defined.',11,1);
return;
end;
if len(@CityName) > 20 begin
raiserror('City name %s is too long. Max 20 is allowed.',11,1,@CityName);
return;
end;
if @CityID is not null and not exists(select * from dbo.City where CityID = @CityID) begin
raiserror('City for update is incorrect CityID = %d not found.',11,1,@CityID);
return;
end;
set @CityName = ltrim(rtrim(@CityName));
--if self-updating - do nothing
if exists (select * from dbo.City where CityName = @CityName and CityID = @CityID) begin
return;
end;
-- any other updates leading to duplicates is not allowed
if exists (select * from dbo.City where CityName = @CityName) begin
raiserror('Duplicate values are not allowed.',11,1);
return;
end;
--update old
if @CityID is not null begin
update dbo.City set CityName = @CityName where CityID = @CityID;
end
--create new
else begin
insert dbo.City(CityName) values (@CityName);
set @CityID = scope_identity();
end;
go
--------------------------
-- Tests
--1 null name
exec dbo.City_save null, null;
go
--2 long name 21
exec dbo.City_save null, 'aabbaabbaabbaabbaabba';
go
--3 wrong CityID
exec dbo.City_save 12345, 'Moscow';
go
--4. create new
declare @CityID smallint = null;
exec dbo.City_save @CityID output, 'Moscow';
select @CityID; --1
go
--5. self update
declare @CityID smallint = 1;
exec dbo.City_save @CityID output, 'Moscow ';
go
--6. create duplicate
exec dbo.City_save null, ' Moscow';
go
--7. update duplicate
declare @CityID smallint = null;
exec dbo.City_save @CityID output, 'Spb';
select @CityID;
exec dbo.City_save @CityID output, 'Moscow ';
go
--8. update
exec dbo.City_save 1, 'Starie Vasyuki';
go
--output
select * from dbo.City;
--drop test data
drop table dbo.City;
-- Create table
create table dbo.City(CityID smallint identity primary key, CityName varchar(20) not null);
create unique nonclustered index ix_CityName on dbo.City(CityName);
go
--------------------------
-- Create proc
create proc [dbo].[City_save]
@CityID smallint=null output,
@CityName varchar(max)=null
AS
set nocount on
--validate params
if @CityName is null begin
raiserror('City name is not defined.',11,1);
return;
end;
if len(@CityName) > 20 begin
raiserror('City name %s is too long. Max 20 is allowed.',11,1,@CityName);
return;
end;
if @CityID is not null and not exists(select * from dbo.City where CityID = @CityID) begin
raiserror('City for update is incorrect CityID = %d not found.',11,1,@CityID);
return;
end;
set @CityName = ltrim(rtrim(@CityName));
--if self-updating - do nothing
if exists (select * from dbo.City where CityName = @CityName and CityID = @CityID) begin
return;
end;
-- any other updates leading to duplicates is not allowed
if exists (select * from dbo.City where CityName = @CityName) begin
raiserror('Duplicate values are not allowed.',11,1);
return;
end;
--update old
if @CityID is not null begin
update dbo.City set CityName = @CityName where CityID = @CityID;
end
--create new
else begin
insert dbo.City(CityName) values (@CityName);
set @CityID = scope_identity();
end;
go
--------------------------
-- Tests
--1 null name
exec dbo.City_save null, null;
go
--2 long name 21
exec dbo.City_save null, 'aabbaabbaabbaabbaabba';
go
--3 wrong CityID
exec dbo.City_save 12345, 'Moscow';
go
--4. create new
declare @CityID smallint = null;
exec dbo.City_save @CityID output, 'Moscow';
select @CityID; --1
go
--5. self update
declare @CityID smallint = 1;
exec dbo.City_save @CityID output, 'Moscow ';
go
--6. create duplicate
exec dbo.City_save null, ' Moscow';
go
--7. update duplicate
declare @CityID smallint = null;
exec dbo.City_save @CityID output, 'Spb';
select @CityID;
exec dbo.City_save @CityID output, 'Moscow ';
go
--8. update
exec dbo.City_save 1, 'Starie Vasyuki';
go
--output
select * from dbo.City;
--drop test data
drop table dbo.City;