August 9, 2012 at 8:47 am
I want a merge procedure for every table in a particular schema, and I have a script to create them. BUT, I know it won't work once I have more complex data types. Is there a way to know (without hard coding) which data types can accept max value, precision and scale?
Here is what I have so far:
declare @statement nvarchar(max) =(
SELECT
' exec sp_executesql @statement = N''CREATE PROCEDURE ' + ISNULL(s.name,'') + '.Save' + ISNULL(o.name,'')
+ ISNULL(STUFF((select ', @' + sc.name + ' ' + st.name + case when st.max_length <> sc.max_length then '(' + convert(varchar,sc.max_length) + ')' else '' end
from sys.columns sc
inner join sys.types st on
sc.system_type_id = st.system_type_id
and sc.user_type_id = st.user_type_id
where
sc.object_id = o.object_id
and sc.is_computed = 0
order by sc.column_id
for xml path('')),1,1,''),'')
+ ' , @isDelete BIT = 0 AS BEGIN MERGE INTO ' + ISNULL(s.name,'') + '.' + ISNULL(o.name,'') + ' AS TARGET '
+ ' USING ( SELECT '
+ ISNULL(STUFF((select ', @' + sc.name + ' as ' + sc.name
from sys.columns sc
inner join sys.types st on
sc.system_type_id = st.system_type_id
and sc.user_type_id = st.user_type_id
and sc.is_computed = 0
where
sc.object_id = o.object_id
order by sc.column_id
for xml path('')),1,1,''),'')
+ ') AS List ON '
+ ISNULL(STUFF((select ' AND TARGET.' + sc.name + ' = ' + 'List.' + sc.name
FROM sys.columns sc
inner join sys.index_columns sic on i.index_id = sic.index_id and i.object_id = sic.object_id and sc.column_id = sic.column_id
where
sc.object_id = o.object_id
and sc.is_computed = 0
for xml path('')),1,4,''),'')
+ ' WHEN MATCHED AND @isDelete = 1 THEN DELETE'
+ ' WHEN MATCHED AND @isDelete = 0 THEN UPDATE SET '
+ ISNULL(STUFF((select ' , TARGET.' + sc.name + ' = ' + 'List.' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ ' WHEN NOT MATCHED AND @isDelete = 0 THEN INSERT ('
+ ISNULL(STUFF((select ' , ' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ ') VALUES ('
+ ISNULL(STUFF((select ' , ' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ '); END'';'
FROM sys.tables o
inner join sys.indexes i on o.object_id = i.object_id and i.is_primary_key = 1
inner join sys.schemas s on o.schema_id = s.schema_id and s.name = '<schema>'
FOR XML PATH(''))
--exec sp_executesql @statement;
select @statement;
I know some of my formatting isn't particularly pretty, but I am open to suggestions on anything.
Here is my forum post about this type of procedure:
http://www.sqlservercentral.com/Forums/Topic1335925-392-1.aspx
To give a better idea of the end result.
Thanks again for the help!
Reading through another post I took out the while loop!
Thanks for making me try new things!
August 9, 2012 at 11:31 am
Not sure it is a good idea, but my way around this is by creating a table with every data type to get the defaults. Then do a comparison with its max, scale, and precision to see if I need to define the type.
Once I have it completed I will post it.
Thanks again,
-Dane
-----------------------------
Update:
This didn't work 🙂
Looks like I will have to hard code the logic...more likely I will just create a table with the information I need to look up...if anyone has a better way to do this it would be greatly appreciated.
August 9, 2012 at 2:25 pm
Not pretty, but it seems to work for me:
I created the following table to help with data type declaration:
declare @DataTypeVariable table
(
name varchar(30),
system_type_id int,
user_type_id int,
max_length char(1),
precision char(1),
scale char(1),
primary key (system_type_id, user_type_id)
)
insert into @datatypevariable (name, system_type_id, user_type_id, max_length, precision, scale)
select
name,
system_type_id,
user_type_id,
/*some of them don't seem to make sense per books on-line, but it doesn't appear that their wording was consistent.*/
case when name in ('varchar','nvarchar','char','nchar','binary','varbinary') then '' else null end as max_length,
case when name in ('decimal','numeric','float','real', 'time') then '' else null end as precision,
case when name in ('decimal','numeric','datetime2','datetimeoffset') then '' else null end as scale
from sys.types
select * from @datatypevariable
I then made the following change to the code to implement this table:
declare @statement nvarchar(max) = '';
set @statement =(
SELECT
' exec sp_executesql @statement = N''CREATE PROCEDURE ' + ISNULL(s.name,'') + '.Save' + ISNULL(o.name,'')
+ ISNULL(STUFF((select ', @' + sc.name + ' ' + dtv.name +
REPLACE(REPLACE(REPLACE(
'('
+ isnull(case when dtv.max_length is null then '' else convert(varchar,sc.max_length / case when dtv.name like 'n%' then 2 else 1 end/*resolving issue with double byte characters*/) end,'')
+ isnull(case when dtv.precision is null then '' else convert(varchar,sc.precision) end,'')
+ isnull(case when dtv.scale is null then '' else case when dtv.precision is not null then ',' else '' end + convert(varchar,sc.scale) end,'')
+ ')'
,'(-1)','(max)'),'(0)','(max)'),'()','')/*-1 is used for max or large data types which is handled in the sub-query below*/
from sys.columns sc
inner join @DataTypeVariable dtv on
sc.system_type_id = dtv.system_type_id
and sc.user_type_id = dtv.user_type_id
where
sc.object_id = o.object_id
and sc.is_computed = 0
order by sc.column_id
for xml path('')),1,1,''),'')
+ ' , @isDelete BIT = 0 AS BEGIN MERGE INTO ' + ISNULL(s.name,'') + '.' + ISNULL(o.name,'') + ' AS TARGET '
+ ' USING ( SELECT '
+ ISNULL(STUFF((select ', @' + sc.name + ' as ' + sc.name
from sys.columns sc
inner join sys.types st on
sc.system_type_id = st.system_type_id
and sc.user_type_id = st.user_type_id
and sc.is_computed = 0
where
sc.object_id = o.object_id
order by sc.column_id
for xml path('')),1,1,''),'')
+ ') AS List ON '
+ ISNULL(STUFF((select ' AND TARGET.' + sc.name + ' = ' + 'List.' + sc.name
FROM sys.columns sc
inner join sys.index_columns sic on i.index_id = sic.index_id and i.object_id = sic.object_id and sc.column_id = sic.column_id
where
sc.object_id = o.object_id
and sc.is_computed = 0
for xml path('')),1,4,''),'')
+ ' WHEN MATCHED AND @isDelete = 1 THEN DELETE'
+ ' WHEN MATCHED AND @isDelete = 0 THEN UPDATE SET '
+ ISNULL(STUFF((select ' , TARGET.' + sc.name + ' = ' + 'List.' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ ' WHEN NOT MATCHED AND @isDelete = 0 THEN INSERT ('
+ ISNULL(STUFF((select ' , ' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ ') VALUES ('
+ ISNULL(STUFF((select ' , ' + sc.name
FROM sys.columns sc
where
sc.object_id = o.object_id
and sc.is_identity = 0
and sc.is_computed = 0
for xml path('')),1,2,''),'')
+ '); END'''
FROM sys.tables o
inner join sys.indexes i on o.object_id = i.object_id and i.is_primary_key = 1
inner join sys.schemas s on o.schema_id = s.schema_id and s.name = '<schema>'
FOR XML PATH(''))
--exec sp_executesql @statement;
select @statement;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply