SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with dynamic sql query


Need help with dynamic sql query

Author
Message
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when i run it:
UPDATE [janinetestdata].[dbo].[EDTE] set ED_THREE = CORRECTED VALUE WHERE zBarcode = 3636 and PERSON_NUMBER = 3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VALUE'.


I printed the statement to check if was an error there, but it printed just fine. I am new to this dynamic query; I have been trying for a few days and can't get it to work. Basically the gist of this program is to check if the command is
UPDATE
then it should set the
ColumnTo Fix
with the value of the
CorrectedValue
.
Below is my code. Any assistance would be greatly appreciated.


USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/

--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT nvarchar(900)


DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX

FROM [janinetestdata].[dbo].[TEST66]


OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six
WHILE @@FETCH_STATUS = 0
BEGIN
/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = '
+ CAST(@CorrectedValue AS nVarchar(15)) + ' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and '
+ 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7))

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)

END/*End of Update Command for Education Table*/

END/*end of Fourteen and Over Commands*/



--execute sp_executesql @CORRECTION_STATEMENT
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six


END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO





Robert klimes
Robert klimes
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2659 Visits: 3421
jancas08 (2/25/2013)
Hi, I need help with this dynamic query to execute the sql statements but it gives the following error when i run it:
UPDATE [janinetestdata].[dbo].[EDTE] set ED_THREE = CORRECTED VALUE WHERE zBarcode = 3636 and PERSON_NUMBER = 3
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'VALUE'.



It is hard to help with this without table structure and some sample data but here is a quick takeaway from what you posted.

in your updates statement quoted above you have the words "CORRECTED VALUE". that is where the incorrect syntax is coming from. If "CORRECTED VALUE" is the string that you are trying to update ED_THREE to then possible changing you query to this might help. note the extra quotes around + CAST(@CorrectedValue AS nVarchar(15)) +

USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/

--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(7),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT nvarchar(900)


DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX

FROM [janinetestdata].[dbo].[TEST66]


OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six
WHILE @@FETCH_STATUS = 0
BEGIN
/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + CAST(@ColumnsToFix AS nVarchar(50))+ ' = '''
+ CAST(@CorrectedValue AS nVarchar(15)) + ''' WHERE zBarcode = ' + CAST(@zBarcode AS nVarchar(15))+ ' and '
+ 'PERSON_NUMBER = ' + CAST(@Person_Number AS nVarchar(7))

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)

END/*End of Update Command for Education Table*/

END/*end of Fourteen and Over Commands*/



--execute sp_executesql @CORRECTION_STATEMENT
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six


END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO



Bob
-----------------------------------------------------------------------------
How to post to get the best help
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
Thanks, for your help. It works....
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25867 Visits: 17509
You could get rid of the cursor here too. Something like this should be pretty close.



DECLARE @CORRECTION_STATEMENT nvarchar(max)

select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDTE] set ' + Columns_To_Fix + ' = '''
+ Corrected_Value + ''' WHERE zBarcode = ' + zBarcode + ' and '
+ 'PERSON_NUMBER = ' + Person_No + ';'
from [janinetestdata].[dbo].[TEST66]
where Command = 'UPDATE'
and Person_No is not null
and Columns_To_Fix is not null
and zBarcode is not null

exec sp_executesql @CORRECTION_STATEMENT



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
Thanks, I will try that now.
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
I tried your way Lange; the only thing is that I can't view the selected data. For example, say the query ends up with 9 rows of data but only row would show because print can only show one row. But it does the execution behind the scenes. Thanks tho.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25867 Visits: 17509
jancas08 (2/26/2013)
I tried your way Lange; the only thing is that I can't view the selected data. For example, say the query ends up with 9 rows of data but only row would show because print can only show one row. But it does the execution behind the scenes. Thanks tho.


Not quite sure what you mean here.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
For example, the select statements finds 10 rows that contain 'UPDATE', it would do the sql execution on the 10 rows that contain 'UPDATE'. But, the use of printing the statement will only show the last row that contain 'UPDATE' because print can't multiple rows in a single instance.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25867 Visits: 17509
jancas08 (2/27/2013)
For example, the select statements finds 10 rows that contain 'UPDATE', it would do the sql execution on the 10 rows that contain 'UPDATE'. But, the use of printing the statement will only show the last row that contain 'UPDATE' because print can't multiple rows in a single instance.


Still not really sure what you are saying. There is only 1 row of dynamic sql now, it contains multiple statements. The code I posted did not have a print statement because there is no loop.


print @CORRECTION_STATEMENT



That will show the entire sql that is going to be executed. It will contain multiple statements now instead of one at a time.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
jancas08
jancas08
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 31
Okay, but its showing only one though. I don't know why. I will show you the one using cursors versus the select state

Below is the cursors


USE [janinetestdata]
GO
/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/

--Variables declaration section
DECLARE @ErrorMessage varchar(500), @BatchNo varchar(15), @zBarcode varchar(15),
@IncorrectValue varchar(100), @ColumnsToFix varchar(25), @Person_Number varchar(5),
@CorrectedValue varchar(100), @Description varchar(500), @Command varchar(10),
@ED_Three varchar(10), @ED_Four varchar(10), @ED_Five varchar(10), @ED_Six varchar(10),
@CORRECTION_STATEMENT Nvarchar(900), @Cluster varchar(15), @HNUM varchar(15),
@District varchar(25), @URBAN_RURAL varchar(100), @Final_Result_Code varchar(25),
@HL_Three varchar(10), @HL_Four varchar(10), @HL_Five varchar(10), @HL_Six varchar(10),
@HL_Seven varchar(10), @HL_SevenB varchar(10),@HHBarcode varchar (10), @Q1_5 varchar(10), @Q1_5_KNS varchar(10),
@Q1_7 varchar(10), @Q1_8 varchar(10), @Q1_9 varchar(10), @Q1_9_2 varchar(10), @Q1_10a varchar(10),
@Q1_10b varchar(10), @Q1_10c varchar(10), @Q1_10_Total varchar(10), @Q1_11 varchar(10), @Q1_12 varchar(10),
@Q1_13 varchar(10), @Q1_14 varchar(10), @Q1_15 varchar(10), @Q1_15a varchar(10),
@Q1_16 varchar(10), @Q1_17 varchar(10), @Q1_18 varchar(10), @Q1_19 varchar(10),
@Q1_20 varchar(10), @Q1_21 varchar(10), @Q1_22 varchar(50), @Q1_23 varchar(50),
@Q1_24 varchar(10), @Q1_25 varchar(10), @Q1_26 varchar(10), @Q1_27 varchar(10),
@Q1_28_MAIN varchar(10), @Q1_28_OTHER varchar(10), @Q1_28_PREVIOUS varchar(10),
@Q1_29_SEASONAL varchar(10), @Q1_29_YEARROUND varchar(10), @Q1_30_MAIN varchar(10),
@Q1_30_OTHER varchar(10), @Q1_30_PREVIOUS varchar(10), @Q1_31_MAIN varchar(10),
@Q1_31_OTHER varchar(10), @Q1_31_PREVIOUS varchar(10), @Q1_32_MAIN varchar(10),
@Q1_32_OTHER varchar(10), @Q1_32_TOTAL varchar(10), @Q1_33_MAIN varchar(10),
@Q1_33_OTHER varchar(10), @Q1_33_TOTAL varchar(10), @Q1_35 varchar(10), @Q1_37 varchar(10),
@Q1_38 varchar(10), @Q1_39 varchar(10), @Q1_40 varchar(10), @Q1_41 varchar(10), @Q1_41_DKNS varchar(10),
@Q1_41a varchar(10), @Q1_41b varchar(10), @Q1_41b_DKNS varchar(10), @Q1_42 varchar(10),
@Q1_42_DKNS varchar(10), @Q1_43 varchar(10), @Q1_44 varchar(10)


DECLARE ERRORCursor CURSOR FOR
SELECT ERRORMESSAGE, BATCH_NO, zBarcode, Incorrect_Value , Columns_To_Fix ,
Person_No , Corrected_Value , Err_Description ,Command , ED_THREE , ED_FOUR
,ED_FIVE, ED_SIX , Cluster, HHNUM , District , URBAN_RURAL, Final_Result_Code,
HL_THREE, HL_FOUR, HL_FIVE, HL_SIX, HL_SEVEN , HL_SEVENB , HHBarcode, Q1_5,
Q1_5_KNS , Q1_7, Q1_8, Q1_9, Q1_9_2, Q1_10a, Q1_10b, Q1_10c, Q1_10_Total, Q1_11,
Q1_12, Q1_13 , Q1_14 , Q1_15, Q1_15a, Q1_16, Q1_17, Q1_18, Q1_19, Q1_20,
Q1_21 , Q1_22, Q1_23 , Q1_24 , Q1_25, Q1_26, Q1_27, Q1_28_MAIN, Q1_28_OTHER,
Q1_28_PREVIOUS , Q1_29_SEASONAL, Q1_29_YEARROUND, Q1_30_MAIN ,Q1_30_OTHER,
Q1_30_PREVIOUS, Q1_31_MAIN, Q1_31_OTHER, Q1_31_PREVIOUS, Q1_32_MAIN , Q1_32_OTHER
,Q1_32_TOTAL, Q1_33_MAIN, Q1_33_OTHER, Q1_33_TOTAL, Q1_35, Q1_37 , Q1_38 , Q1_39
,Q1_40, Q1_41, Q1_41_DKNS, Q1_41a, Q1_41b, Q1_41b_DKNS, Q1_42, Q1_42_DKNS, Q1_43 ,Q1_44

FROM [janinetestdata].[dbo].[TEST]


OPEN ERRORCursor
BEGIN
--Fetch first household
FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue,
@ColumnsToFix ,@Person_Number, @CorrectedValue ,
@Description, @Command, @ED_Three , @ED_Four ,
@ED_Five , @ED_Six ,@Cluster , @HNUM ,
@District , @URBAN_RURAL , @Final_Result_Code ,
@HL_Three, @HL_Four, @HL_Five , @HL_Six ,
@HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS ,
@Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a,
@Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 ,
@Q1_13 , @Q1_14 , @Q1_15 , @Q1_15a,
@Q1_16 , @Q1_17 , @Q1_18, @Q1_19,
@Q1_20 , @Q1_21 , @Q1_22, @Q1_23,
@Q1_24 , @Q1_25 , @Q1_26 , @Q1_27,
@Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS,
@Q1_29_SEASONAL , @Q1_29_YEARROUND , @Q1_30_MAIN,
@Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN,
@Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN ,
@Q1_32_OTHER , @Q1_32_TOTAL , @Q1_33_MAIN,
@Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37,
@Q1_38 , @Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS ,
@Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42,
@Q1_42_DKNS , @Q1_43 , @Q1_44

WHILE @@FETCH_STATUS = 0
BEGIN

/*Beginning of Education Commands*/
if @ErrorMessage LIKE 'ED%'
BEGIN/*Beginning of Update Command for Education Table*/
if @Command = 'UPDATE' and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(15)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ' and '
+ 'PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)

END/*End of Update Command for Education Table*/

/*Beginning of Insert Command for Education Table*/
else if @Command = 'INSERT' and @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES('+
coalesce(CAST(@ED_THREE AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Four AS nVarchar(10)),'99999') + ','
+ coalesce(CAST(@ED_Five AS nVarchar(10)),'99999') + ',' + coalesce(CAST(@ED_Six AS nVarchar(10)),'99999')+ ','
+ coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ',' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999')+ ')'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Education Table*/

/*Beginning of Delete Command for Education Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END
END
END/*End of Delete Command for Education Table*/
END
/*End of Education Commands*/

/*Beginning of HH_ALL Commands*/
else if @ErrorMessage LIKE 'HH%'
BEGIN/*Beginning of Update Command for HH_ALL Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'Person_No = ' +coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for HH_ALL Table*/

/*Beginning of Insert Command for HH_ALL Table*/
else if @Command = 'INSERT'and @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES('+
coalesce(CAST(@BatchNo AS nVarchar(8)),'99999') + ' , ' + coalesce(CAST(@Cluster AS nVarchar (10)),'99999') + ' , '
+ coalesce(CAST(@HNUM AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , '
+ coalesce(CAST(@District AS nVarchar(10)),'99999')+ ' , ' +
coalesce(CAST(@URBAN_RURAL AS nVarchar(10)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(10)),'99999')
+ ' , ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ' )'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END /*End of INSERT Command for HH_ALL Table*/

/*Beginning of Delete Command for HH_ALL Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = '
+ coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999')

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END /*End of Delete Command for HH_ALL Table*/
END
END /*End Else*/
END/*end of HH_ALL Commands*/

/*Beginning of Household Listing Commands*/
else if @ErrorMessage LIKE 'HL%'
BEGIN/*Beginning of Update Command for Household Listing Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[HH_LISTING] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Household Listing Table*/

/*Beginning of Insert Command for Household Listing Table*/
else if @Command = 'INSERT'and @Person_Number is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('''+
coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@HL_Three AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@HL_Four AS nVarchar (10)),'99999')
+ ' , ' + coalesce(CAST(@HL_Five AS nVarchar(10)),'99999') + ' , '+ coalesce(CAST(@HL_Six AS nVarchar(10)),'99999')+ ' , ' + coalesce(CAST(@HL_Seven AS nVarchar (10)),'99999')
+ ' , ' + coalesce(CAST(@HL_SevenB AS nVarchar (10)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' )'
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Household Listing Table*/



/*Beginning of Delete Command for Household Listing Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and PERSON_NUMBER = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Delete Command for Household Listing Table*/
END
END/*end else*/
END /*end of Household Listing Commands*/

/*Beginning of Fourteen and Over Commands*/
else if @ErrorMessage LIKE 'O14%'
BEGIN/*Beginning of Update Command for Fourteen and Over Table*/
if @Command = 'UPDATE'and @Person_Number is not null and @ColumnsToFix is not null
and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set ' + coalesce(CAST(@ColumnsToFix AS nVarchar(50)),'99999')+ ' = '
+ coalesce(CAST(@CorrectedValue AS nVarchar(50)),'99999')+ ' WHERE zBarcode = ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and '
+ 'Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''
print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Update Command for Fourteen and Over Table*/


/*Beginning of Insert Command for Fourteen and Over Table*/
else if @Command = 'INSERT'and @Person_Number is not null and @Q1_8 is not null and @Q1_23 is not null and @Q1_22 is not null and
@Q1_30_MAIN is not null and @Q1_30_OTHER is not null and @Q1_30_PREVIOUS is not null and @Q1_31_MAIN is not null and @Q1_31_OTHER is not null
and @Q1_31_PREVIOUS is not null and @Q1_39 is not null and @Q1_40 is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES('+
coalesce(CAST(@BatchNo AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' , ' + coalesce(CAST(@HHBarcode AS nVarchar(7)),'99999')
+ ' , ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@District AS nVarchar(7)),'99999') + ' , '+ coalesce(CAST(@URBAN_RURAL AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Cluster AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@HNUM AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Final_Result_Code AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_5 AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_5_KNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_7 AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_8 AS nVarchar(7)),'99999') + ''' , '+
coalesce(CAST(@Q1_9 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_9_2 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10b AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_10c AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_10_Total AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_11 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_12 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_13 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_14 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_15 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_15a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_16 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_17 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_18 AS nVarchar(7)),'99999') + ' , '+
coalesce(CAST(@Q1_19 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_20 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_21 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_22 AS nVarchar(7)),'99999')+ ''' , '''
+ coalesce(CAST(@Q1_23 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_24 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_25 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_26 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_27 AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_28_MAIN AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_28_PREVIOUS AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_29_SEASONAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_29_YEARROUND AS nVarchar(7)),'99999') + ' , '''+ coalesce(CAST(@Q1_30_MAIN AS nVarchar(7)),'99999') +''' , ''' +
coalesce(CAST(@Q1_30_OTHER AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_30_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ''' + coalesce(CAST(@Q1_31_MAIN AS nVarchar(7)),'99999') + ''' , ''' +
coalesce(CAST(@Q1_31_OTHER AS nVarchar(7)),'99999') + ''' , '''+ coalesce(CAST(@Q1_31_PREVIOUS AS nVarchar(7)),'99999')+ ''' , ' + coalesce(CAST(@Q1_32_MAIN AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_32_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_32_TOTAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_MAIN AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_33_OTHER AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_33_TOTAL AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_35 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_37 AS nVarchar(7)),'99999') + ' , '
+ coalesce(CAST(@Q1_38 AS nVarchar(7)),'99999') + ' , ''' + coalesce(CAST(@Q1_39 AS nVarchar(7)),'99999') + ''' , ''' + coalesce(CAST(@Q1_40 AS nVarchar(7)),'99999') + ''' , ' + coalesce(CAST(@Q1_41 AS nVarchar(7)),'99999') + ' , ' +
coalesce(CAST(@Q1_41_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41a AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_41b_DKNS AS nVarchar(7)),'99999')
+ ' , ' + coalesce(CAST(@Q1_42 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_42_DKNS AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_43 AS nVarchar(7)),'99999') + ' , ' + coalesce(CAST(@Q1_44 AS nVarchar(7)),'99999') + ')'

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of INSERT Command for Fourteen and Over Table*/


/*Beginning of Delete Command for Fourteen and Over Table*/
else
BEGIN
if @Command = 'DELETE'
BEGIN
if @Person_Number is not null and @zBarcode is not null
BEGIN
set @CORRECTION_STATEMENT = 'DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = '+
coalesce(CAST(@zBarcode AS nVarchar(15)),'99999') + ' and Person_No = ''' + coalesce(CAST(@Person_Number AS nVarchar(7)),'99999') + ''''

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)
END/*End of Delete Command for Fourteen and Over Table*/
END/*end else*/
END
END/*end of Fourteen and Over Commands*/


FETCH NEXT FROM ERRORCursor into
@ErrorMessage, @BatchNo , @zBarcode,@IncorrectValue , @ColumnsToFix ,
@Person_Number,@CorrectedValue , @Description, @Command,
@ED_Three , @ED_Four , @ED_Five , @ED_Six ,
@Cluster , @HNUM ,
@District , @URBAN_RURAL , @Final_Result_Code ,
@HL_Three, @HL_Four, @HL_Five , @HL_Six ,
@HL_Seven , @HL_SevenB ,@HHBarcode , @Q1_5 , @Q1_5_KNS ,
@Q1_7, @Q1_8 , @Q1_9 , @Q1_9_2 , @Q1_10a,
@Q1_10b , @Q1_10c , @Q1_10_Total, @Q1_11 , @Q1_12 ,
@Q1_13 , @Q1_14 , @Q1_15 , @Q1_15a,
@Q1_16 , @Q1_17 , @Q1_18, @Q1_19,
@Q1_20 , @Q1_21 , @Q1_22, @Q1_23,
@Q1_24 , @Q1_25 , @Q1_26 , @Q1_27,
@Q1_28_MAIN , @Q1_28_OTHER , @Q1_28_PREVIOUS,
@Q1_29_SEASONAL , @Q1_29_YEARROUND , @Q1_30_MAIN,
@Q1_30_OTHER , @Q1_30_PREVIOUS , @Q1_31_MAIN,
@Q1_31_OTHER , @Q1_31_PREVIOUS, @Q1_32_MAIN ,
@Q1_32_OTHER , @Q1_32_TOTAL , @Q1_33_MAIN,
@Q1_33_OTHER , @Q1_33_TOTAL , @Q1_35 , @Q1_37,
@Q1_38 , @Q1_39 , @Q1_40, @Q1_41 , @Q1_41_DKNS ,
@Q1_41a, @Q1_41b , @Q1_41b_DKNS , @Q1_42,
@Q1_42_DKNS , @Q1_43 , @Q1_44


END/*End of begin*/
END
CLOSE ERRORCursor
DEALLOCATE ERRORCursor
GO





It prints out

UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 1 WHERE zBarcode = 15394 and PERSON_NUMBER = 3

(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 2 WHERE zBarcode = 567 and PERSON_NUMBER = 1

(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2

(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,4,6,2,4398)

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 4

(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 5

(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6

(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15110 and Person_No = 1

(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 20657 and Person_No = 1

(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1

(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36144 , 2 , 3 , 21227 , 5 , 6 , 8 , 1 )

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )

(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'

(3 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = '3'

(3 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 21098 and PERSON_NUMBER = '3'

(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'

(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 63 WHERE zBarcode = 51415 and Person_No = '2'

(1 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36122 , 21593 , 3 , '1' , 4 , 4 , 7 , 9 , 3 , 4 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gif

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36124 , 22019 , 2 , '1' , 2 , 4 , 7 , 9 , 4 , 6 , 4 , 6 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3667 and Person_No = '1'

(0 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'

(0 row(s) affected)




The reason some of them said 0 rows affected because the data had been already deleted; I have ran the program like 10 times.


Below is the one using your syntax


/*Purpose of the sql query is to make corrections to all the errors detected
which in essences pertains to all of the tables concerning the LFS Survey .
This query will run insertions, updates and deletion commands*/

--Variables declaration section
DECLARE @CORRECTION_STATEMENT nvarchar(max)

/*Beginning of Update Command for Education Table*/
select @CORRECTION_STATEMENT = 'UPDATE [janinetestdata].[dbo].[EDUCATION] set '
+ coalesce(CAST(Columns_To_Fix AS Varchar(50)),'99999')+ ' = '
+ coalesce(CAST(Corrected_Value AS Varchar(15)),'99999')+
' WHERE zBarcode = ' + coalesce(CAST(zBarcode AS Varchar(15)),'99999')+ ' and '
+ 'PERSON_NUMBER = ' + coalesce(CAST(Person_No AS Varchar(7)),'99999')
from [janinetestdata].[dbo].[TEST]
where Command = 'UPDATE'
and Person_No is not null
and Columns_To_Fix is not null
and zBarcode is not null
and ERRORMESSAGE LIKE 'ED%'

print @CORRECTION_STATEMENT
EXECUTE (@CORRECTION_STATEMENT)






UPDATE [janinetestdata].[dbo].[EDUCATION] set ED_THREE = 3 WHERE zBarcode = 567 and PERSON_NUMBER = 2

(0 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[EDUCATION] VALUES(2,3,7,5,3,4237)

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[EDUCATION] WHERE zBarcode = 20725 and PERSON_NUMBER = 6

(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_ALL] set Final_Result_Code = 0 WHERE zBarcode = 15141 and Person_No = 2

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_ALL] VALUES(36157 , 2 , 3 , 5678 , 5 , 6 , 8 , 1 )

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_ALL] WHERE zBarcode = 21265 and Person_No = 1

(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[HH_LISTING] set HL_FOUR = 3 WHERE zBarcode = 21555 and PERSON_NUMBER = 3

(3 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[HH_LISTING] VALUES('1' , 3 , 4 , 6 , 8 , 6 , 8 , 15011 )

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[HH_LISTING] WHERE zBarcode = 24723 and PERSON_NUMBER = '1'

(0 row(s) affected)
UPDATE [janinetestdata].[dbo].[FOURTEEN_ALL] set Q1_42_DKNS = 67 WHERE zBarcode = 50913 and Person_No = '2'

(1 row(s) affected)
INSERT INTO [janinetestdata].[dbo].[FOURTEEN_ALL] VALUES(36124 , 22019 , 2 , '1' , 2 , 4 , 7 , 9 , 4 , 6 , 4 , 6 , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , '5' , '5' , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , '5' , '5' , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5 , 5)

(1 row(s) affected)
DELETE FROM [janinetestdata].[dbo].[FOURTEEN_ALL] WHERE zBarcode = 3674 and Person_No = '1'

(0 row(s) affected)





As you can see the results, differ; your syntax only outputs the last result of the select statement.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search