Sql dynamic query

Question

Post reply

Sql dynamic query

bladerunner148

SSCommitted

Points: 1824
More actions
July 1, 2010 at 2:01 pm

#145755

Hi there,
I have to produce the following result set.
Gender Count Percentage
Male 4000 0.4
Female 6000 0.6
In my table, the Males are 4000 and Females are 6000. On the interface, there will be number of columns for the users to select in a dropdown..so this column (eg Gender) is going to be dynamic.
Here is what I have done so far...my percentage is not calculated.
ALTER PROCEDURE SA_STPR_Demographics_Select
@Country varchar(255),
@State varchar(50),
@Demographics varchar(255)
AS
BEGIN
DECLARE @sql NVARCHAR(4000), @Count int, @UnknownFull varchar(50), @UnknownShort varchar(1)
SET@UnknownFull = 'Unknown'
SET@UnknownShort = 'U'
SET@Count =(
SELECTCOUNT(*)
FROMCustomers_monthly
WHERECountry = @Country
ANDState = @State
)
SET@sql = 'SELECT[' + @Demographics + '],
COUNT(*),
CAST(COUNT(*) / (SELECT COUNT(*) FROM Customers_monthly WHERE Country = ''' + @Country + ''' AND State = ''' + @State + ''' AND ([' + @Demographics + '] != ''' + @UnknownFull + ''' OR [' + @Demographics + '] != ''' + @UnknownShort + ''')) As decimal)
FROMCustomers_monthly
WHERECountry = ''' + @Country + '''
ANDState = ''' + @State + '''
AND([' + @Demographics + '] != ''' + @UnknownFull + '''
OR[' + @Demographics + '] != ''' + @UnknownShort + ''')
GROUP BY [' + @Demographics + ']'
EXECUTE (@SQL)
END

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Any chance to be a little more specific?

E.g. by providing table def and sample data of the table involved together with a few scenarios to cover?

This would actually help us to have something to test our solutions against.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

bladerunner148 (7/1/2010)
Hi there,
I have to produce the following result set.
Gender Count Percentage
Male 4000 0.4
Female 6000 0.6
In my table, the Males are 4000 and Females are 6000. On the interface, there will be number of columns for the users to select in a dropdown..so this column (eg Gender) is going to be dynamic.
Here is what I have done so far...my percentage is not calculated.
ALTER PROCEDURE SA_STPR_Demographics_Select
@Country varchar(255),
@State varchar(50),
@Demographics varchar(255)
AS
BEGIN
DECLARE @sql NVARCHAR(4000), @Count int, @UnknownFull varchar(50), @UnknownShort varchar(1)
SET@UnknownFull = 'Unknown'
SET@UnknownShort = 'U'
SET@Count =(
SELECTCOUNT(*)
FROMCustomers_monthly
WHERECountry = @Country
ANDState = @State
)
SET@sql = 'SELECT[' + @Demographics + '],
COUNT(*),
CAST(COUNT(*) / (SELECT COUNT(*) FROM Customers_monthly WHERE Country = ''' + @Country + ''' AND State = ''' + @State + ''' AND ([' + @Demographics + '] != ''' + @UnknownFull + ''' OR [' + @Demographics + '] != ''' + @UnknownShort + ''')) As decimal)
FROMCustomers_monthly
WHERECountry = ''' + @Country + '''
ANDState = ''' + @State + '''
AND([' + @Demographics + '] != ''' + @UnknownFull + '''
OR[' + @Demographics + '] != ''' + @UnknownShort + ''')
GROUP BY [' + @Demographics + ']'
EXECUTE (@SQL)
END

Except for a redundant query and opening yourself up for a healthy dose of SQL Injection, what's wrong with what you have?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bladerunner148 SSCommitted Points: 1824 More actions · Answer 3

I have tweaked my query and now it works. I am sorry if this has become redundant. Also, could you point out if I am allowing sql injection anywhere on my query. I just couldn't find a better way to do this I guess.

ALTER PROCEDURE SA_STPR_Demographics_Select

@Country varchar(255),

@State varchar(50),

@Demographics varchar(255)

AS

BEGIN

DECLARE @sql NVARCHAR(4000)

SET@sql = 'SELECTCOUNT(*) AS CustomerCount,

[' + @Demographics + ']

INTO #tempDemo

FROMCustomers_monthly

WHERECountry = ''' + @Country + '''

ANDState = ''' + @State + '''

GROUP BY [' + @Demographics + ']'

SET @sql = @sql+'SELECT [' + @Demographics + '] as Data,

SUM(customercount) as [Count],

CAST((CAST( SUM(customercount)AS DECIMAL(14,4)) /CAST((Select SUM(customercount)

FROM #tempdemo WHERE ([' + @Demographics + ']<>''Unknown'' OR [' + @Demographics + ']<>''U'')) AS DECIMAL(14,4)))AS DECIMAL (14,4)) AS Percentage

INTO #tempsort

FROM #tempDemo

WHERE ([' + @Demographics + ']<>''Unknown'' OR [' + @Demographics + ']<>''U'')

GROUP BY [' + @Demographics + ']

SELECT t.*

FROM #tempsort t

INNER JOIN Demographics d

ON replace (t.[Data],'' '','''')=replace(d.[Range],'' '','''')

WHEREd.[demographicsName]=''' + @Demographics + '''

ORDER BY d.[sort order]'

EXECUTE (@SQL)

END

Any suggestion would be greatly appreciated. Thanks!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

You do allow sql injection in every place where you perform string concatination. To avoid it - use sp_executesql.

What is your table structure and what are you passing in @Demographics parameter?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

bladerunner148 SSCommitted Points: 1824 More actions · Answer 5

Table structure:

CREATE TABLE [dbo].[Customers_monthly](

[Intuitive] [nvarchar](5) NULL,

[Intuitive Cust ID] [nvarchar](255) NULL,

[Solomons] [nvarchar](5) NULL,

[Solomons Cust ID] [nvarchar](255) NULL,

[COMPANY] [nvarchar](255) NULL,

[Contact Title] [nvarchar](255) NULL,

[Salutation] [nvarchar](255) NULL,

[First Name] [nvarchar](255) NULL,

[Last Name] [nvarchar](255) NULL,

[Gender] [nvarchar](255) NULL,

[ADDRESS 1] [nvarchar](255) NULL,

[ADDRESS 2] [nvarchar](255) NULL,

[CITY] [nvarchar](255) NULL,

[STATE] [nvarchar](255) NULL,

[ZIP] [nvarchar](255) NULL,

[ZIP4] [nvarchar](255) NULL,

[COUNTY] [nvarchar](255) NULL,

[COUNTRY] [nvarchar](255) NULL,

[PHONE] [nvarchar](255) NULL,

[Email] [binary](510) NULL,

[MOVETYPE] [nvarchar](255) NULL,

[MOVEDATE] [nvarchar](255) NULL,

[Telephone Number10] [nvarchar](255) NULL,

[Fax Number10] [nvarchar](255) NULL,

[Toll Free Number10] [nvarchar](255) NULL,

[Web Address] [nvarchar](255) NULL,

[Industry Code] [nvarchar](1) NULL,

[Industry Descr] [nvarchar](50) NULL,

[2 Digit SIC] [nvarchar](2) NULL,

[2 Digit SIC Descr] [nvarchar](100) NULL,

[Primary SIC] [nvarchar](255) NULL,

[Primary SIC Descr] [nvarchar](255) NULL,

[Customer Category] [nvarchar](255) NULL,

[Secondary SIC1] [nvarchar](255) NULL,

[Secondary SIC1 Descr] [nvarchar](255) NULL,

[Secondary SIC2] [nvarchar](255) NULL,

[Secondary SIC2 Descr] [nvarchar](255) NULL,

[Employee Size Code] [nvarchar](255) NULL,

[Employee Size Descr] [nvarchar](255) NULL,

[Anual Sales Code] [nvarchar](255) NULL,

[Annual Sales Descr] [nvarchar](255) NULL,

[Years Business Started] [varchar](50) NULL,

[InfoUSA ID] [nvarchar](255) NULL,

[Individual Firm] [nvarchar](255) NULL,

[Business Status] [nvarchar](25) NULL,

[Business Credit Score Description] [nvarchar](255) NULL,

[Growing Shrinking Indicator] [nvarchar](255) NULL,

[Square Footage Code] [nvarchar](255) NULL,

[Square Footage Descr] [nvarchar](255) NULL,

[Work At Home Indicator] [nvarchar](255) NULL

) ON [PRIMARY]

In the interface, I have the following columns in a dropdownlist:

Gender

Customer Category

Employee Size Descr

Annual Sales Descr

Years Business Started

Business Status

Business Credit Score Description

Growing Shrinking Indicator

Square Footage Descr

Work At Home Indicator

So, in my stored procedure, @Demographics could be one of the fields from this dropdownlist.

Let me know if you need to know anything else. Thanks!