Difference between dynamic query execution and static query execution

Question

Difference between dynamic query execution and static query execution

subramani.rudrappa 78855

SSC Eights!

Points: 848
More actions
December 6, 2012 at 3:55 am

#271214

Hi,
i have requirement to read data array and the same is implemented as below
Method1:
declare @SampleArray nvarchar(max) ='1,2,3'
BEGIN
DECLARE @SQL1 varchar(max)
SET @SQL1 =
' select name from dbo.Proposal where ProposalId in ('+ @SampleArray +')'
END
Method 2:
declare @SampleArray nvarchar(max) ='1,2,3'
BEGIN
select name from dbo.Proposal where ProposalId in (@SampleArray)
END
Question:
Method 1 is by using dynamic Sql and it is fetching me correct result.
Method 2 is by static sql and it is throwing error "Error converting data type nvarchar to bigint."
how is dynamic sql is overcoming this error????

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

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

BrainDonor SSCoach Points: 19254 More actions · Answer 1

In the first query, print @SQL1 after the execution and have a look at what code that is actually running, then you'll see the difference between the two queries.

Steve Hall
Linkedin
Blog Site

subramani.rudrappa 78855 SSC Eights! Points: 848 More actions · Answer 2

Hi,

if i use Print SQL

i am getting below query and it is fine.

select name from dbo.Proposal where ProposalId in (1,2,3)

where as in static sql it is thrrowing error cannot convert varchar to bigint.

how is dynamic sql is able to overcome this error?

BrainDonor SSCoach Points: 19254 More actions · Answer 3

Because the first query is running:

IN (1,2,3)

and the second query is running:

IN(@SampleArray)

which is the equivalent of IN('1,2,3') because it is a varchar. Please note the '' characters around the numbers.

SQL is trying to convert this to match the data type of ProposalID (which I assume is an int) and having a problem, because '1,2,3' can't be converted to an int, because there are commas within it.

Steve Hall
Linkedin
Blog Site

subramani.rudrappa 78855 SSC Eights! Points: 848 More actions · Answer 4

Hi Brain,

first query also running as

' select name from dbo.Proposal where ProposalId in ('+ @SampleArray +')'

let me re frame the question while it is passed to dynamic SQL it is passing

select name from dbo.Proposal where ProposalId in (1,2,3)

but why in static sql it is passing

select name from dbo.Proposal where ProposalId in ('1,2,3')

how quotes are getting ommited in dynamic sql??

BrainDonor SSCoach Points: 19254 More actions · Answer 5

In the dynamic query you are actually joining three string of character together and then using the EXEC command, which is designed to execute a command stored within a string data type.

Joining strings together does not also include any implied quotation marks.

DECLARE @Stringdata VARCHAR(MAX);

DECLARE @String2 VARCHAR(MAX);

SET @String2 = 'characters '

SET @Stringdata = 'This ' + 'is ' + 'a ' + 'string ' + 'of ' + @String2 + 'without any quotes.';

PRINT @Stringdata;

The result of this query does not contain a quotation mark and if you wanted it to you have to cater for them - but that's another problem we'll ignore for now.

So your dynamic query is select name from dbo.Proposal where ProposalId in (1,2,3) because the quotes aren't part of it.

However, method 2 is different. All it sees is that @SampleArray is a varchar and is trying to compare it to an int. It needs to make the two fields in the comparison compatible types, so it looks at the value of @SamplArray and tries to convert 1,2,3 (including the commas) to an integer and has a problem with this.

Steve Hall
Linkedin
Blog Site