Using Column Names as Parameters

Question

Using Column Names as Parameters

timotech

SSCrazy

Points: 2698
More actions
April 21, 2016 at 6:55 am

#400052

Good day all,
Please how can i use this column name dynamically to query a table without writing separate queries to do the search.
This is sample code:
declare @Parameters nvarchar(max), @Criteria nvarchar(4000)
set @Parameters = 'Male'
set @Criteria = 'Gender'
Select Medium, RespondentMobile, Gender, QuestionNo, Answers from tbl_MediumResults
Where @Criteria = @Parameters
Its returning nulls for me
Thanks for your response

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

You can't parameterise column names.

If you have a small number of options, separate queries (or better separate procedures). Since the query implies there are only a few columns in the table, I'd recommend this approach.

If there are a lot of possible column names, then you'll need dynamic SQL and you MUST, MUST, MUST check that the column names passed are real column names and you MUST parameterise the dynamic SQL. If you mess either up, you create a huge security vulnerability.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timotech SSCrazy Points: 2698 More actions · Answer 2

Thanks for your reply,

so how do i do the dynamic sql?

Tim

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

I strongly recommend, with only a few columns like you appear to have, to avoid dynamic SQL.

It's easy to get wrong, and it's prone to horrible security flaws (used, for example, in the Sony playstation hacks) that can cause data disclosure.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

timotech SSCrazy Points: 2698 More actions · Answer 4

GilaMonster (4/21/2016)
I strongly recommend, with only a few columns like you appear to have, to avoid dynamic SQL.
It's easy to get wrong, and it's prone to horrible security flaws (used, for example, in the Sony playstation hacks) that can cause data disclosure.

Thanks GilaMonster for your replies,

Actually its just a small scenerio for a larger path of code.

As you have suggested i will have to go the long way instead of looking for a short cut.

But i have another question, i have this cte:

;with tmp as (

select RespondentName, PhoneNumber1, MaritalStatus, Region, Urbanisation, Religion, Gender, Location, AgeGroup, DayOfInterview, PhoneUsed, case when isnumeric(split1.item) = 1 then dbo.tbl_TabletsUsed.Description else split1.item end as TabletsUsed From (

SELECT TOP (100) PERCENT dbo.tbl_Respondents.RespondentName, dbo.tbl_Respondents.PhoneNumber1, dbo.tbl_MaritalStatus.Description AS MaritalStatus, dbo.tbl_Region.Description AS Region,

dbo.tbl_Urbanisation.Description AS Urbanisation, dbo.tbl_Religion.Description AS Religion, dbo.tbl_Gender.Description AS Gender, dbo.tbl_Locations.Description AS Location,

dbo.tbl_AgeGroup.Description AS AgeGroup, dbo.tbl_DayOfInterview.Description AS DayOfInterview, case when isnumeric(split2.item) = 1 then dbo.tbl_PhoneUsed.Description else split2.item end as PhoneUsed, COALESCE (NULLIF (dbo.tbl_Respondents.TabletUsed, ''), 'None') as TabletUsed

FROM dbo.tbl_Respondents INNER JOIN

dbo.tbl_MaritalStatus ON REPLACE(dbo.tbl_Respondents.FK_MaritalStatusId, ',', '') = dbo.tbl_MaritalStatus.PKID INNER JOIN

dbo.tbl_Region ON REPLACE(dbo.tbl_Respondents.FK_RegionId, ',', '') = dbo.tbl_Region.PKID INNER JOIN

dbo.tbl_Urbanisation ON REPLACE(dbo.tbl_Respondents.FK_UrbanizationId, ',', '') = dbo.tbl_Urbanisation.PKID INNER JOIN

dbo.tbl_Religion ON REPLACE(dbo.tbl_Respondents.FK_ReligionId, ',', '') = dbo.tbl_Religion.PKID INNER JOIN

dbo.tbl_Gender ON REPLACE(dbo.tbl_Respondents.FK_GenderId, ',', '') = dbo.tbl_Gender.PKID INNER JOIN

dbo.tbl_AgeGroup ON REPLACE(dbo.tbl_Respondents.FK_AgeGroupId, ',', '') = dbo.tbl_AgeGroup.PKID INNER JOIN

dbo.tbl_Locations ON REPLACE(dbo.tbl_Respondents.FK_LocationId, ',', '') = dbo.tbl_Locations.PKID INNER JOIN

dbo.tbl_DayOfInterview ON REPLACE(dbo.tbl_Respondents.DayOfInterview, ',', '') = dbo.tbl_DayOfInterview.PKID

CROSS APPLY dbo.DelimitedSplit8K(dbo.tbl_Respondents.PhoneUsed, ',') AS split2

left JOIN dbo.tbl_PhoneUsed ON case when isnumeric(split2.item) = 1 then split2.item end = dbo.tbl_PhoneUsed.PKID

Where split2.item > '') as d

CROSS APPLY dbo.DelimitedSplit8K(TabletUsed, ',') AS split1

left JOIN dbo.tbl_TabletsUsed ON case when isnumeric(split1.item) = 1 then split1.item end = dbo.tbl_TabletsUsed.PKID

Where split1.item > ''),

tmp2 as (

Select '' as Criteria,

SUM(case when DayOfInterview = 'Monday' then DaysOfInt Else 0 End) as 'Monday',

SUM(case when DayOfInterview = 'Tuesday' then DaysOfInt Else 0 End) as 'Tuesday',

SUM(case when DayOfInterview = 'Wednesday' then DaysOfInt Else 0 End) as 'Wednesday',

SUM(case when DayOfInterview = 'Thursday' then DaysOfInt Else 0 End) as 'Thursday',

SUM(case when DayOfInterview = 'Friday' then DaysOfInt Else 0 End) as 'Friday',

SUM(case when DayOfInterview = 'Saturday' then DaysOfInt Else 0 End) as 'Saturday',

SUM(case when DayOfInterview = 'Sunday' then DaysOfInt Else 0 End) as 'Sunday',

SUM(case when Urbanisation = 'Urban' then TotUrban else 0 end) as 'Urban',

SUM(case when Urbanisation = 'Semi-Urban' then TotUrban else 0 end) as 'Semi-Urban',

SUM(case when Gender = 'Male' then TotGend else 0 end) as 'Male',

SUM(case when Gender = 'Female' then TotGend else 0 end) as 'Female',

SUM(case when AgeGroup = '8-11' then TotAge else 0 end) as '8-11years',

SUM(case when AgeGroup = '12-17' then TotAge else 0 end) as '12-17years',

SUM(case when AgeGroup = '18-24' then TotAge else 0 end) as '18-24years',

SUM(case when AgeGroup = '25-34' then TotAge else 0 end) as '25-34years',

SUM(case when AgeGroup = '35-44' then TotAge else 0 end) as '35-44years',

SUM(case when AgeGroup = '45-54' then TotAge else 0 end) as '45-54years',

SUM(case when AgeGroup = '55-64' then TotAge else 0 end) as '55-64years',

SUM(case when AgeGroup = '65+' then TotAge else 0 end) as '65+years',

SUM(case when Religion = 'Christian' then TotRel else 0 end) as 'Christian',

SUM(case when Religion = 'Muslim' then TotRel else 0 end) as 'Muslim',

SUM(case when Religion = 'Others' then TotRel else 0 end) as 'Others',

SUM(case when MaritalStatus = 'Single' then TotMar else 0 end) as 'Single',

SUM(case when MaritalStatus = 'Married' then TotMar else 0 end) as 'Married',

SUM(case when MaritalStatus = 'Widowed' then TotMar else 0 end) as 'Widowed',

SUM(case when MaritalStatus = 'Divorced' then TotMar else 0 end) as 'Divorced'

From (

Select DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar from tmp

group by DayOfInterview,Urbanisation, Gender, AgeGroup, Religion, MaritalStatus) as t)

Select Criteria, Monday, Tuesday, Wednesday from tmp2

union all

Select @Parameters, Monday, Tuesday, Wednesday from tmp2 where tmp.Gender = 'Male'

I want to be able to get the Gender field from the tmp cte, but i cant seem to get to it, can you please look at my code and how i can get to the Gender field

Thanks so much

Tim

timotech SSCrazy Points: 2698 More actions · Answer 5

Well guys,

I think i found a solution to my code problem, the solution was to use a recursive cte and group by the field you want, that gives you exactly what you need: code below

;with tmp(

DayOfInterview, DaysOfInt, Urbanisation, TotUrban, Gender, TotGend, AgeGroup, TotAge, Religion, TotRel, MaritalStatus, TotMar) as (

Select DayOfInterview, COUNT(DayOfInterview) as DaysOfInt, Urbanisation, COUNT(Urbanisation) as TotUrban, Gender, COUNT(Gender) as TotGend, AgeGroup, COUNT(AgeGroup) as TotAge, Religion, COUNT(Religion) as TotRel, MaritalStatus, COUNT(MaritalStatus) as TotMar from (