have a table in which there are comma delimited fields, I want to update them based on conditionsif my additional_procedure location field contains null or not 1 and 2 i need to make it 1I am using following method 1) spiliting into rows 2) checking those values where ever there is null, not 1 and not 2, set it to 13) again making those rows into comma delimited field 4) I want to put that new comma delimited field into table here is temp table [code="sql"] drop Table #temp Create Table #temp (MRN nchar(10) null, additional_procedure varchar(max), additional_procedure_Location varchar(max)) insert into #temp (MRN,additional_procedure,additional_procedure_Location) values('1','9772800,9773100,9773200', '1,2,3'),('2','9773300, 9773400, 9773500, 9773600', '2,1,,1'),('3','9774300,9774400,9774500,9774600','1,2,2,4'),('4','9775300,9775400,9776100','4,,2'),('5','9776500,9776800,9776900,9777100','2,1,,1')Select * from #tempI wrote 2 table valued functions first one to spilit them into rows and than another to find out if there is null or not 1 and not 2 and for setting value to 1I also made a procedure in which I am passing two parameters and getting the new comnplete field in comma delimited format , however I am unable toput this new comma delimited field into the table and need help how to update it, your help would be highly appreciated my functions are as belowFirst Function to spilit these comma delimited fields into rows [code="sql"]GO/****** Object: UserDefinedFunction [dbo].[Validate_2_DelimitedFields_Id] Script Date: 05/22/2012 15:27:51 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[Validate_2_DelimitedFields_Id]( @delimited1 nvarchar(max), @delimited2 nvarchar(max), @delimiter1 nvarchar(100), @delimiter2 nvarchar(100), @StartFieldNum int, @EndFieldNum int) RETURNS @t TABLE( id int, val1 nvarchar(max), val2 nvarchar(max))ASBEGIN insert into @t(id,val1,val2) select a.id,a.val,b.val from [HealthValidator].[dbo].[ValidateDelimitedField] (@delimited1,',',1,50) as a full outer join [HealthValidator].[dbo].[ValidateDelimitedField] (@delimited2,',',1,50) as b on a.Id = b.Id RETURNENDGO[/code]Second funtion to find where is not 1 not 2 and null values and setting it to 1 [code="sql"]CREATE FUNCTION [dbo].[UpdateProcedureLocation] ( -- Add the parameters for the function here @ap varchar(max), @apl varchar(max) )RETURNS @ReturnTable TABLE ( -- Add the column definitions for the TABLE variable here [id] int NULL, [ap] [varchar](max) NULL, [apl] [varchar](max) NULL )ASBEGIN INSERT INTO @ReturnTable(id,ap,apl) Select * from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26) Update @ReturnTable Set apl = '1' where Id in (Select id from dbo.Validate_2_DelimitedFields_id(@ap, @apl ,',',',',1,26) where ((nullif(val1,'') is not null ) and nullif(val2,'') is null) or (nullif(val1,'') is not null ) and (val2 <> '2' and val2 <> '1')) RETURN END[/code]and third procedure in which I am passing these two and getting a new field in the same comma delimited format [code="sql"]create proc [dbo].[Sp_UpdateProcedureLocation] @ap varchar(Max), @apl varchar(Max), @newapl varchar(Max) output as declare @TEMPTable TABLE (id int, ap varchar(20), apl varchar(20)) Insert into @TEMPTable(id,ap,apl) Select * from dbo.[UpdateProcedureLocation](@ap, @apl) DECLARE @NewProcLoc VARCHAR(MAX) SELECT @NewProcLoc = COALESCE(@NewProcLoc+',' ,'') + apl FROM @temptable SELECT @NewProcLoc Set @newapl = @NewProcLoc GO[/code]My required output is [code="sql"] drop Table #temp Create Table #temp (MRN nchar(10) null, additional_procedure varchar(max), additional_p[/code]rocedure_Location varchar(max)) insert into #temp (MRN,additional_procedure,additional_procedure_Location) values('1','9772800,9773100,9773200', '1,2,1'),('2','9773300, 9773400, 9773500, 9773600', '2,1,1,1'),('3','9774300,9774400,9774500,9774600','1,2,2,1'),('4','9775300,9775400,9776100','1,1,2'),('5','9776500,9776800,9776900,9777100','2,1,1,1')Select * from #temp[/code]Umar Memon |
Updating comma delimited field
Posted - 2012-05-22 : 02:13:23
