Passing parameters to identify columns in SELECT statement 1

[TonyBoex]

I am trying to create a stored procedure which will return counts so that I can graph the results. I am using a GROUP BY ... WITH ROLLUP to create the default recordset which will have the count column, and two others (selected by the user). I would like to pass the names of the user selected columns as @Column1 and @Column2, along with the other query parameters when the stored procedure is called. Unfortunately I cannot figure out the syntax so that the resulting recordset lists all the values in @Column1 and @Column2 (currently it just prints the associated field name vice the field value. Thanks for the help.

Desired Result
Count Column1 Column2
10 EXC F14
5 FLA F14
26 ROU F14
8 EXC P3
. . .
. . .

Current Result
Count Column1 Column2
10 tblMishapFactors.[3rdLevelCode_FK] tblMishaps.Aircraft_FK
5 tblMishapFactors.[3rdLevelCode_FK] tblMishaps.Aircraft_FK
26 tblMishapFactors.[3rdLevelCode_FK] tblMishaps.Aircraft_FK
8 tblMishapFactors.[3rdLevelCode_FK] tblMishaps.Aircraft_FK
. . .
. . .


Create Procedure spGraphQuery2
(
@AC_Type varchar(100) = NULL,
@Mishap_Type varchar(50) = NULL,
@Mishap_Class varchar(50) = NULL,
@Location varchar(50) = NULL,
@Service varchar(100) = NULL,
@Year int = NULL,
@1stLevel varchar(255) = NULL,
@2ndLevel varchar(255) = NULL,
@3rdLevel varchar(255) = NULL,
@Column1 varchar(100) ='tblMishapFactors.[3rdLevelCode_FK]',
@Column2 varchar(100) ='tblMishaps.Aircraft_FK'
)

As

Set nocount on

SELECT Count(MishapID) as Count, @Column1, @Column2
FROM tblMishaps INNER JOIN tblMishapFactors ON tblMishaps.MishapID =
tblMishapFactors.MishapID_FK INNER JOIN
tblFactors ON tblMishapFactors.[3rdLevelCode_FK] = tblFactors.[3rdLevelCode]
WHERE tblMishaps.Aircraft_FK = COALESCE(@AC_Type, tblMishaps.Aircraft_FK) AND
tblMishaps.Type_FK = COALESCE(@Mishap_Type, tblMishaps.Type_FK) AND
tblMishaps.Class_FK = COALESCE(@Mishap_Class, tblMishaps.Class_FK) AND
tblMishaps.LocationID_FK = COALESCE(@Location, tblMishaps.LocationID_FK) AND
tblMishaps.OrgID_FK = COALESCE(@Service, tblMishaps.OrgID_FK) AND
Year(tblMishaps.MishapDate) = COALESCE(@Year, Year(tblMishaps.MishapDate)) AND
tblFactors.[1stLevelCode] = COALESCE(@1stLevel, tblFactors.[1stLevelCode]) AND
tblFactors.[2ndLevelCode] = COALESCE(@2ndLevel, tblFactors.[2ndLevelCode]) AND
tblMishapFactors.[3rdLevelCode_FK] = COALESCE(@3rdLevel, tblMishapFactors.[3rdLevelCode_FK])
GROUP BY @Column1, @Column2 WITH ROLLUP

Return

 

[tlbroadbent]

You cannot use parameters for table names, columns names, etc. You need to dynamically create the SQL statement. Use the following in place of the SELECT statemnt in your SP. This code will place your select statement in a variable and then execute the variable via the Execute statement.

Declare @sql nvarchar(2048)

Set @sql='SELECT Count(MishapID) as Count, ' + @Column1 + ', ' + @Column2 +
' FROM tblMishaps INNER JOIN tblMishapFactors ON tblMishaps.MishapID =
tblMishapFactors.MishapID_FK INNER JOIN
tblFactors ON tblMishapFactors.[3rdLevelCode_FK] = tblFactors.[3rdLevelCode]
WHERE tblMishaps.Aircraft_FK = COALESCE(@AC_Type, tblMishaps.Aircraft_FK) AND
tblMishaps.Type_FK = COALESCE(@Mishap_Type, tblMishaps.Type_FK) AND
tblMishaps.Class_FK = COALESCE(@Mishap_Class, tblMishaps.Class_FK) AND
tblMishaps.LocationID_FK = COALESCE(@Location, tblMishaps.LocationID_FK) AND
tblMishaps.OrgID_FK = COALESCE(@Service, tblMishaps.OrgID_FK) AND
Year(tblMishaps.MishapDate) = COALESCE(@Year, Year(tblMishaps.MishapDate)) AND
tblFactors.[1stLevelCode] = COALESCE(@1stLevel, tblFactors.[1stLevelCode]) AND
tblFactors.[2ndLevelCode] = COALESCE(@2ndLevel, tblFactors.[2ndLevelCode]) AND
tblMishapFactors.[3rdLevelCode_FK] = COALESCE(@3rdLevel, tblMishapFactors.[3rdLevelCode_FK])
GROUP BY ' + @Column1 + ', ' + @Column2 + ' WITH ROLLUP'

Execute @sql Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[TonyBoex]

I tried the code suggested by tlbroadbent within my stored procedure but can't get it to return any records. I've gotten the following code to work by passing the dynamic SQL statement using "Set rsGraph = cmd.Execute" within my ASP. I would really like to use the stored procedure and parameters listed on my previous post so that I minimize the roundtrips to the server. Any Ideas?


'Build parameter list with Mishap Factors drop-down selections (NULL values not stored)
If Len(Request.Form("cboAircraft&quot)>0 Then
strParam = "tblMishaps.Aircraft_FK = '" & Trim(Request.Form("cboAircraft&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cboType&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblMishaps.Type_FK = '" & Trim(Request.Form("cboType&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cboClass&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblMishaps.Class_FK = '" & Trim(Request.Form("cboClass&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cboLocation&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblMishaps.LocationID_FK = '" & Trim(Request.Form("cboLocation&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cboService&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblMishaps.OrgID_FK = '" & Trim(Request.Form("cboService&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cboYear&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "Year(tblMishaps.MishapDate) = " & Trim(Request.Form("cboYear&quot)
blnMultiple=True
End If

If Len(Request.form("cbo1stLevelFactors&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblFactors.[1stLevelCode] = '" & Trim(Request.Form("cbo1stLevelFactors&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cbo2ndLevelFactors&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblFactors.[2ndLevelCode] = '" & Trim(Request.Form("cbo2ndLevelFactors&quot) & "'"
blnMultiple=True
End If

If Len(Request.form("cbo3rdLevelFactors&quot)>0 Then
If blnMultiple Then
strParam = strParam & " AND "
End if
strParam = strParam & "tblFactors.[3rdLevelCode] = '" & Trim(Request.Form("cbo3rdLevelFactors&quot) & "'"
blnMultiple=True
End If


End If


If Len(Request.form("cboColumn1&quot)>0 Then
strColumn1 = Request.form("cboColumn1&quot
Else
strColumn1 = "tblMishapFactors.[3rdLevelCode_FK]"
End if

If Len(Request.form("cboColumn2&quot)>0 Then
strColumn2 = Request.form("cboColumn2&quot
Else
strColumn2 = "Year(DateAdd(month,3,tblMishaps.MishapDate))"
End if


strSelect = "SELECT COUNT(tblMishaps.MishapID) as Count, " & strColumn1 & " as Column1, " & strColumn2 & " as Column2 "
strFrom = "FROM tblMishaps INNER JOIN tblMishapFactors ON tblMishaps.MishapID = tblMishapFactors.MishapID_FK INNER JOIN tblFactors ON tblMishapFactors.[3rdLevelCode_FK] = tblFactors.[3rdLevelCode] "
If Trim(strParam) <> &quot;&quot; Then
strWhere = &quot;WHERE &quot; & Trim(strParam) & &quot; &quot;
Else
strWhere = &quot;&quot;
End if
strGroupBy = &quot;GROUP BY &quot; & strColumn1 & &quot;, &quot; & strColumn2 & &quot; WITH ROLLUP&quot;

strSQL = strSelect & strFrom & strWhere & strGroupBy

cmd.CommandText=strSQL
Set rsGraph = cmd.Execute