I am trying to update a field with data from another table. Normally this is a no brainer however this time I have a comma delimited field that contains the primary key(s) to data in another table. On top of that the data field isn't always clean. For example the field I'm trying to update can look like any of the following:
-2- Physical Access Control / Building Security
ient Admitting and Registration
and some like this one which is only 2 pieces of information not 3
eduling, Scripting and Automation Tools
So far I've tried several things (function, stored procedure, dynamic sql) and always keep coming back at a stalemate when attempting to update this field (I actually have 3 fields with this same issue). I did create a procedure that does seem to work, but only for a single record at a time so I'm sure there has to be a better way - I don't want to use a cursor, and I can't manipulate any kind of table within a function.
I am trying to return a name for the ones with ID's, the name when the names matches, and, ideally, the name with a flag (asterisk or something) for ones don't match on either name or ID.
Below is the procedure I currently run to return "names". Any thoughts on how to improve this so it operates on the entire table without a cursor are greatly appreciated. Thanks.
DECLARE @AppID VARCHAR(25) SET @AppID = 'APP006670'
DECLARE @SQL VARCHAR(2000)
DECLARE @MApptypeID VARCHAR(500)
DECLARE @Apptype VARCHAR(5000)
SET @MApptypeID =
'''' + replace
WHEN isnull(cast(atn."u_name" AS VARCHAR (500)),'none') = 'none'
THEN cast("u_application_type" AS VARCHAR(500))
ELSE cast(atn."u_name" AS VARCHAR(500))
END,',',''',''') + ''''
left outer join dbo.Apptype atn
ON cast(app.u_application_type AS VARCHAR(500))=cast(atn.sys_id AS VARCHAR(500))
cast("u_number" AS VARCHAR(50)) = @AppID
TRUNCATE TABLE dbo.MApptype
SET @SQL =
'INSERT INTO dbo.MApptype
WHERE [sys_id] IN (' + @MApptypeID + ')
WHERE [u_name] IN (' + @MApptypeID + ')'
SELECT @Apptype = coalesce(@Apptype + ',', '') + u_name FROM dbo.MApptype