sql dynaminc and aggregate functions

Question

sql dynaminc and aggregate functions

Lou

SSCrazy

Points: 2929
More actions
March 27, 2014 at 1:29 pm

#148377

Hi,
Just looking if somebody has experienced, i have written a tql code query something like, and run ok:
Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score From MyTable Group By Grade
Is possible that If I embed this code in an expression like:
Set @extsql = ' Select Grade, (Count(Grade)* 100 / (Select Count(*) '
Set @extsql = @extsql+ ' From MyTable)) as Score From MyTable '
Set @extsql = @extsql+ ' Group set By Grade'
Execute (@extsql)
Our final result, were very distinct that when I run this query normally, Are the any constraint when you calculate anything using dynamic sql ?,
Any Idea would be appreciate

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

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

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

When you execute the code, SQL Server doesn't care if the code is dynamic or static. As long as the instruction in the dynamic code becomes exactly the same code as your static code.

You should always review your code by printing it when you're testing it.

It might be part of the obfuscation, but your codes aren't exactly the same. That could be happening in your actual query.

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

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

To me the bigger question is why are you using dynamic sql at all? From what you posted there is absolutely no need for it whatsoever.

_______________________________________________________________

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/

Lowell SSC Guru Points: 323518 More actions · Answer 3

another issue is that query is going to use integer division and return incorrect totals

something in the query needs to be multiplied by 1.0 so that the datatype is expanded beyond integers.

1/3 = 0

1/(3 * 1.0) = 0.33

Select Grade, (Count(Grade)* 100 / (Select Count(*) From MyTable)) as Score

Select Grade, (Count(Grade)* (100 * 1.0) / (Select Count(*) From MyTable)) as Score

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lou SSCrazy Points: 2929 More actions · Answer 4

Thanks to all,

Your labor,ideas and toughts inspire ..