September 14, 2015 at 12:01 pm
I am trying to get this to work without having to write a cursor or something like that. I think it should be able to be done with a complex query, but can't seem to figure out how. I have tried a couple things with a case statement in the select but keep getting conversion errors. If someone could please just provide me with some guidance of how I may go about doing this to work, that would be great. Not looking for the solution, just a pointer in which direction to go.
I have this query
select sc.name, <errormessage here>
from sys.objects so
join sys.columns sc on sc.object_id = so.object_id
join sys.types st on st.system_type_id = sc.system_type_id
join #import_memberdata_layout iml on iml.name = sc.name
where so.name = 'import_memberdata'
and (iml.system_type_id <> sc.system_type_id
or iml.max_length <> sc.max_length
or iml.column_id <> sc.column_id
or iml.is_nullable <> sc.is_nullable)
order by sc.column_id
I want to include an errormessage after the sc.name field depending on which of the items in the where clause
do not match. so if it is because of the max_length field not matching, what would display would be something like this.
Memberkey, Max length does not match. system length = 30. implementation length = 35
Field name = memberkey 30 is the value of the sc.max_length field and the 35 is the value of the iml.max_length field.
The temp table definition is
create table #Import_memberdata_layout
(
name varchar (30)
,System_type_id int
,max_length int
,column_id int
,is_nullable int
)
Thank you so much for your help,
Christine
September 14, 2015 at 1:06 pm
christine 23290 (9/14/2015)
I am trying to get this to work without having to write a cursor or something like that. I think it should be able to be done with a complex query, but can't seem to figure out how. I have tried a couple things with a case statement in the select but keep getting conversion errors. If someone could please just provide me with some guidance of how I may go about doing this to work, that would be great. Not looking for the solution, just a pointer in which direction to go.I have this query
select sc.name, <errormessage here>
from sys.objects so
join sys.columns sc on sc.object_id = so.object_id
join sys.types st on st.system_type_id = sc.system_type_id
join #import_memberdata_layout iml on iml.name = sc.name
where so.name = 'import_memberdata'
and (iml.system_type_id <> sc.system_type_id
or iml.max_length <> sc.max_length
or iml.column_id <> sc.column_id
or iml.is_nullable <> sc.is_nullable)
order by sc.column_id
I want to include an errormessage after the sc.name field depending on which of the items in the where clause
do not match. so if it is because of the max_length field not matching, what would display would be something like this.
Memberkey, Max length does not match. system length = 30. implementation length = 35
Field name = memberkey 30 is the value of the sc.max_length field and the 35 is the value of the iml.max_length field.
The temp table definition is
create table #Import_memberdata_layout
(
name varchar (30)
,System_type_id int
,max_length int
,column_id int
,is_nullable int
)
Thank you so much for your help,
Christine
Quick suggestion, use a CASE clause for the error message
😎
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply