Help with dynamic SQL

Question

Help with dynamic SQL

Briceston

SSCrazy

Points: 2899
More actions
April 7, 2015 at 1:59 pm

#299739

Hi guys,
I have a dynamic SQL script that errors out. Can you guys provide some insight on how to resolve the error?
Code:
select
t1.name,
t1.column_id
into #tbl_columns
from tempdb.sys.columns t1
where object_id = object_id('tempdb..#tbl_CH');
-- drop table #tbl_claims
select distinct
t1.ClaimNumber,
R1 = RANK() over (order by t1.ClaimNumber)
into #tbl_claims
from Test.dbo.tbl_DataX_CH t1
declare @mf int
declare @mc int
declare @if int
declare @ic int
declare @s-2 nvarchar(1000)
declare @claimNumber nvarchar(1000)
declare @field nvarchar(1000)
declare @fieldProduction nvarchar(1000)
declare @fieldTest nvarchar(1000)
set @mf = ( select max(column_id) from #tbl_columns )
set @s-2 = ''
set @if = 1
set @ic = 1
set @mc = ( select max(R1) from #tbl_claims )
while @ic <= @mc
begin
set @claimNumber = (select ClaimNumber from #tbl_claims where R1 = @ic )
while @if <= @mf
begin
set @field = (select name from #tbl_columns where column_id = @if )
set @s-2 = N'select @result = ' + @field + N' from Test.dbo.tbl_DataX_CH where ClaimNumber = ''' + @claimNumber + N''''
execute sp_executesql @s-2, N'@result nvarchar(1000) output', @result=@fieldProduction output
print (@s)
set @s-2 = N'select @result = ' + @field + N' from #tbl_CH ClaimNumber = ''' + @claimNumber + N''' '
execute sp_executesql @s-2, N'@result nvarchar(1000) output', @result=@fieldTest output
set @s-2 = N'insert into #tbl_comparison values (''' + @claimNumber + N''', ''' + @field + N''', ''' + @fieldProduction + N''', ''' + @fieldTest + N'''); '
exec(@s)
set @if = @if + 1
end
set @ic = @ic + 1
end

Error codes:
select @result = VersionNumber from Test.dbo.tbl_DataX_CH where ClaimNumber = '01/08/2015-002-09'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
select @result = ClaimNumber from Test.dbo.tbl_DataX_CH where ClaimNumber = '01/08/2015-002-09'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
select @result = PayorName from Test.dbo.tbl_DataX_CH where ClaimNumber = '01/08/2015-002-09'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
select @result = GroupNumber from Test.dbo.tbl_DataX_CH where ClaimNumber = '01/08/2015-002-09'
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

Can you include the DDL for the temp tables?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Briceston SSCrazy Points: 2899 More actions · Answer 2

Alan.B (4/7/2015)
Can you include the DDL for the temp tables?

Added it.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

The "DDL" is not good as we can't use it.

Some questions:

Why are you concatenating values instead of parametrizing your queries?

Are you aware of SQL injection?

Why are you using RBAR in the form of nested while loops instead of a set based option?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Briceston SSCrazy Points: 2899 More actions · Answer 4

Alan.B (4/7/2015)
Can you include the DDL for the temp tables?

Here's a sample of the data from the temp table:

"into #tbl_claims"

ClaimNumber R1

01/08/2015-002-091

01/08/2015-006-572

01/08/2015-006-593

01/08/2015-006-714

01/08/2015-006-725

01/08/2015-006-786

01/08/2015-700-067

01/08/2015-700-078

01/08/2015-700-089

01/08/2015-700-0910

"into #tbl_columns"

Name Column_id

VersionNumber1

ClaimNumber2

PayorName 3

GroupNumber 4

GroupName 5

SSN 6

MemberID 7

AltMemberID8

FullName 9

Address1 10

Briceston SSCrazy Points: 2899 More actions · Answer 5

Luis Cazares (4/7/2015)
The "DDL" is not good as we can't use it.
Some questions:
Why are you concatenating values instead of parametrizing your queries?
Are you aware of SQL injection?
Why are you using RBAR in the form of nested while loops instead of a set based option?

I'm not the original author, this code was past on to me to try figure out why it's not working. However, thank you for asking these valid question. I will definitely look into why the said approach was not taking into consideration when it was being written.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I think that I found the problem. Is easy to notice it if you format your strings.

I'm tempted to post the corrected code but I'd love to have the opportunity to give a better option for this process that removes the double looping. I just need the complete set of tables.

We need DDL and sample data for Test.dbo.tbl_DataX_CH and #tbl_CH.

I'm not sure if I should post the code with parametrized queries for your dynamic sql.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2