Need help with dynamic sql query

Question

Need help with dynamic sql query

Viewing 11 posts - 31 through 40 (of 40 total)

You must be logged in to reply to this topic. Login to reply

jancas08 SSC Veteran Points: 266 More actions · Answer 1

Yup its just inserting a new row. I can't change the structure of the tables because I don't have the 'authority' to do so. I am just to implement the code for insertion deletion and updates.

jancas08 SSC Veteran Points: 266 More actions · Answer 2

declare @sql varchar(max)

--First is the updates

select @sql = stuff ((

select

'Update dbo.' +

case

when ERRORMESSAGE LIKE 'ED*' then 'Education'

when ERRORMESSAGE LIKE 'O14*' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL*' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL*' then 'HH_ALL'

end

+ ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

+ ''' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

from test

where Command = 'UPDATE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

--Now we append the deletes

select @sql = @sql + stuff ((

select

'DELETE dbo.' +

case

when ERRORMESSAGE LIKE 'ED*' then 'Education'

when ERRORMESSAGE LIKE 'O14*' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL*' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL*' then 'HH_ALL'

end

+ ' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

from test

where Command = 'UPDATE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

select @sql

--exec sp_execute @sql

I modified your code a little bit because the errormessage would be long like this:

O14 ERROR 2_3: The person cluster variable( 39) does not match that on the HH record ( 49)

So, I had to change it to like; but when I do this it says :

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NULL

(1 row(s) affected)

How can it be only 1 row and it should be multiple rows affected?

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

You have the wrong wildcard in your LIKE. The asterisk is from Access. SQL Server use percent.

when ERRORMESSAGE LIKE 'ED%' then 'Education'

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

jancas08 (2/27/2013)
Yup its just inserting a new row. I can't change the structure of the tables because I don't have the 'authority' to do so. I am just to implement the code for insertion deletion and updates.

I feel your pain there. Sadly sometimes we are left to plug our nose and plod ahead dealing the garbage somebody else left behind. 😛

_______________________________________________________________

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

jancas08 SSC Veteran Points: 266 More actions · Answer 5

Thanks, I know right. Sigh.....Okay, I have to bother you one more time.

It outputs this:

[p]

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Update dbo.Education set ED_THREE = '1' where zBarcode = 15394 and PERSON_NUMBER = 3;Update dbo.Education set ED_THREE = '2' where zBarcode = 567 and PERSON_NUMBER = 1;Update dbo.Education set ED_THREE = '3' where zBarcode = 567 and PERSON_NUMBER = 2;Updat

(1 row(s) affected)

[/p]

I can't see the rest of commands and i left @sql intialized to max

jshahan SSCarpal Tunnel Points: 4622 More actions · Answer 6

jshahan

SSCarpal Tunnel

Points: 4622

February 27, 2013 at 3:50 pm

#1591783

deleted for lack of relevance...

jancas08 SSC Veteran Points: 266 More actions · Answer 7

Okay this is what I have done. An update of everything. I am still not able see the entirety of the command it cuts off the screen.

declare @sql varchar(max)

--First is the updates

select @sql = stuff ((

select

'Update dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

end

+ ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

+ ''' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

from test

where Command = 'UPDATE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

select @sql

--Now we append the deletes

select @sql = @sql + stuff ((

select

'DELETE dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

end

+ ' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';' as MyVal

from test

where Command = 'DELETE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

--Now we append the deletes

select @sql = @sql + stuff ((

select

'INSERT INTO dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education' +

+'VALUES('+

coalesce(CAST(ED_THREE AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Four AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Five AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Six AS varchar(10)),'99999')+ ','

+ coalesce(CAST(Person_No AS varchar(7)),'99999') + ','

+ coalesce(CAST(zBarcode AS varchar(15)),'99999')+ ')'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL' +

'VALUES('+

coalesce(CAST(Batch_No AS varchar(7)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , ' + coalesce(CAST(HHBarcode AS varchar(7)),'99999')

+ ' , ''' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(District AS varchar(7)),'99999') + ' , '+ coalesce(CAST(URBAN_RURAL AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(Cluster AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(HHNUM AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_5 AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(Q1_5_KNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_7 AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_8 AS varchar(7)),'99999') + ''' , '+

coalesce(CAST(Q1_9 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_9_2 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10b AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(Q1_10c AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_10_Total AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_11 AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_12 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_13 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_14 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_15 AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_15a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_16 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_17 AS varchar(7)),'99999') + ''' , ' +coalesce(CAST(Q1_18 AS varchar(7)),'99999') + ' , '+

coalesce(CAST(Q1_19 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_20 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_21 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_22 AS varchar(7)),'99999')+ ''' , '''

+ coalesce(CAST(Q1_23 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_24 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_25 AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_26 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_27 AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(Q1_28_MAIN AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_28_PREVIOUS AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_29_SEASONAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_29_YEARROUND AS varchar(7)),'99999') + ' , '''+ coalesce(CAST(Q1_30_MAIN AS varchar(7)),'99999') +''' , ''' +

coalesce(CAST(Q1_30_OTHER AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_30_PREVIOUS AS varchar(7)),'99999')+ ''' , ''' + coalesce(CAST(Q1_31_MAIN AS varchar(7)),'99999') + ''' , ''' +

coalesce(CAST(Q1_31_OTHER AS varchar(7)),'99999') + ''' , '''+ coalesce(CAST(Q1_31_PREVIOUS AS varchar(7)),'99999')+ ''' , ' + coalesce(CAST(Q1_32_MAIN AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_32_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_32_TOTAL AS varchar(7)),'99999') + ' , ' +coalesce(CAST(Q1_33_MAIN AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_33_OTHER AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_33_TOTAL AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_35 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_37 AS varchar(7)),'99999') + ' , '

+ coalesce(CAST(Q1_38 AS varchar(7)),'99999') + ' , ''' + coalesce(CAST(Q1_39 AS varchar(7)),'99999') + ''' , ''' + coalesce(CAST(Q1_40 AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(Q1_41 AS varchar(7)),'99999') + ' , ' +

coalesce(CAST(Q1_41_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41a AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_41b_DKNS AS varchar(7)),'99999')

+ ' , ' + coalesce(CAST(Q1_42 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_42_DKNS AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_43 AS varchar(7)),'99999') + ' , ' + coalesce(CAST(Q1_44 AS varchar(7)),'99999') + ')'

when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'+ 'VALUES('''+

coalesce(CAST(Person_No AS varchar(7)),'99999') + ''' , ' + coalesce(CAST(HL_Three AS varchar (10)),'99999') + ' , ' + coalesce(CAST(HL_Four AS varchar (10)),'99999')

+ ' , ' + coalesce(CAST(HL_Five AS varchar(10)),'99999') + ' , '+ coalesce(CAST(HL_Six AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(HL_Seven AS varchar (10)),'99999')

+ ' , ' + coalesce(CAST(HL_SevenB AS varchar (10)),'99999') + ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' )'

when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'+ 'VALUES('+

coalesce(CAST(BATCH_NO AS varchar(8)),'99999') + ' , ' + coalesce(CAST(Cluster AS varchar (10)),'99999') + ' , '

+ coalesce(CAST(HHNUM AS varchar(10)),'99999')+ ' , ' + coalesce(CAST(zBarcode AS varchar(15)),'99999') + ' , '

+ coalesce(CAST(District AS varchar(10)),'99999')+ ' , ' +

coalesce(CAST(URBAN_RURAL AS varchar(10)),'99999') + ' , ' + coalesce(CAST(Final_Result_Code AS varchar(10)),'99999')

+ ' , ' + coalesce(CAST(Person_No AS varchar(7)),'99999') + ' )'

end

+ ';' as MyVal

from test

where Command = 'INSERT'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

select @sql

--exec sp_execute @sql

jancas08 SSC Veteran Points: 266 More actions · Answer 8

Lange I got it.....I got it....here is the final....

declare @sql varchar(max)

--First is the updates

select @sql = stuff ((

select

'Update dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

end

+ ' set ' + Columns_To_Fix + ' = ''' + Corrected_Value

+ ''' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal

from test

where Command = 'UPDATE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

--select @sql

--Now we append the deletes

select @sql = @sql + stuff ((

select

'DELETE dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL'

when ERRORMESSAGE LIKE 'HL%' then 'HH_LISTING'

when ERRORMESSAGE LIKE 'HL%' then 'HH_ALL'

end

+ ' where zBarcode = ' + cast(zBarcode as varchar(20))

+ ' and PERSON_NUMBER = ' + cast(Person_No as varchar(20)) + ';'+ CHAR(13) as MyVal

from test

where Command = 'DELETE'

for XML PATH(''), type

).value('.','varchar(max)'), 1, 0, '')

--Now we append the deletes

select @sql = @sql + stuff ((

select

'INSERT INTO dbo.' +

case

when ERRORMESSAGE LIKE 'ED%' then 'Education' +

+' VALUES('+

coalesce(CAST(ED_THREE AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Four AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Five AS varchar(10)),'99999') + ','

+ coalesce(CAST(ED_Six AS varchar(10)),'99999')+ ','

+ coalesce(CAST(Person_No AS varchar(7)),'99999') + ','

+ coalesce(CAST(zBarcode AS varchar(15)),'99999')+ ')'

when ERRORMESSAGE LIKE 'O14%' then 'FOURTEEN_ALL' +

' VALUES('+