Dynamic Column Selection in Stored Procedure

Hey Guys,

Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @.part = 'o-ring' or @.sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @.part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.

DECLARE @.querycolumn varchar(20),
@.queryvalue varchar(35)

SET @.querycolumn = ''
SET @.queryvalue = ''

IF(@.sub_assembly = NULL)
BEGIN
IF(@.part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @.querycolumn = 'Part'
SET @.queryvalue = @.part
END
END
ELSE
BEGIN
SET @.querycolumn = 'SubAssembly'
SET @.queryvalue = @.sub_assembly
END

SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @.querycolumn = @.queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part

The problem is that I'm getting an error when I try to use @.querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?you should build a command string and then execute it by EXEC()

try this:

DECLARE @.querycolumn varchar(20), @.queryvalue varchar(35), @.command varchar(200)

SET @.command = ''
...
...
...
SET @.command = 'SELECT SubAssembly, Part, COUNT(RecordID) FROM Table
WHERE '+@.querycolumn+' = '''+@.queryvalue+''' GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part'

EXEC(@.command)

it should help...|||How About:

Declare @.subAssembly nvarchar(215), @.queryColumn nvarchar(216), @.part nvarchar(214)
Declare @.t Table( RecordId int identity(1,1), Part nvarchar(215), SubAssembly nvarchar(214) )

Select @.part = Null --N'Part A'
, @.subAssembly = N'Sub Zero'

Insert @.t (Part, SubAssembly)
Select 'Part A', 'Sub A'
Union
Select 'Part B', 'Sub B'
Union
Select 'Part A', 'Sub Zero'
Union
Select 'Part B', 'Sub Zero'

Select Case
When @.part Is Not Null Then Part
End As 'Part'
, Case
When @.subAssembly Is Not Null Then SubAssembly
End As 'SubAssembly'
, Count(RecordId) As 'Count'
From @.t
Where part = Coalesce(@.part, part)
And SubAssembly = Coalesce(@.SubAssembly, SubAssembly)
Group By Case
When @.part Is Not Null Then Part
End
, Case
When @.subAssembly Is Not Null Then SubAssembly
End

Select * From @.t|||I forgot to mention that the select statement is going to be used in a cursor.

DECLARE generic_cursor CURSOR FOR
EXEC(@.command)

throws an error|||Good lord why?

Gonna make the FETCH dynamic as well?

You have to understand that dynamic sql executes in a separate spid...so your sproc won't access to that session, and it will disappear when the execution is complete.

What are you actually trying to do?|||...and throwing a cursor into the mess too...|||Just say no to cursors.|||Ok, I'll take a stab at this... I'd start with something like:SELECT Count(*)
FROM (
SELECT
p.SubAssembly, p.Part
FROM Table AS p
WHERE p.Part = @.part
UNION SELECT
s.SubAssembly, s.Part
FROM Table AS s
WHERE s.SubAssembly = @.SubAssembly
) AS zIt makes no sense to me to try grouping, since the groups will make no sense at all. This code avoids cursors, although it could be used to create one (although why you'd do that is beyond me). It also uses the UNION to sidestep the need for dynamic code too.

The question is, will it help tianmingqing at all?

-PatP|||Okay, so what I am trying to accomplish here is to create a stored procedure (main routine) that calls another stored procedure (sub routine) that creates a cursor based on parameters passed in by the main routine. I was trying to make that cursor creation dynamic so that I didn't have to write four versions of the same query where only one line in the where clause changes. Maybe I need to rethink this...|||...but why the cursor?

Even mentioning cursors on this forum starts raising concerns, because 9 time out of 10 (at least) they are not necessary and just complicate the code.|||because I need to take each row from the cursor one at a time and compare it with the results from another query that uses the same parameters to make sure that the counts match. If they don't, then I need to return the two counts, and the parameter values so that I know where I have a problem in a process I am running. Thus the reason for the cursor is to iterate through a results set. Is that not a good use for a cursor, and why the disdain for them? Sorry, most of my previous SQL work has been done in relative isolation with help from BOL, so I'm somewhat of a newbie.|||Cursors are really leftovers from previous technologies. They are effectively hang-ons from the logic of unit-record equipment where everything had to be handled one card at a time.

Other database products still use cursors heavily. As a matter of fact, some database engines still rely on cursors for nearly everything that they do.

There is nothing intrinsicly wrong with cursors. As a matter of fact, they are still the only practical way to handle a few tasks such as mass-renaming objects, managing logins, etc.

However, from a performance perspective cursors are at least one order of magnitude slower than similar set-based solutions to the same problem. They are often much worse than that!

For your problem, a simple join of the two queries would get you exactly what you've asked for, with lots less code, and would be much more efficient than using cursors to do the same task.

This begs my original question from my previous post... Did the solution I suggested help you?

-PatP|||...but why the cursor?

Even mentioning cursors on this forum starts raising concerns, because 9 time out of 10 (at least)

More...9.99999|||It would be interesting to keep stats on this.

Raise your hand if you have ever found a cursor in somebody else's code and the use of a cursor actually WAS required...

Hmm...I'm not smelling a lot of armpits...|||Okay, so due to the overwhelming suggestions go forego the cursor, I decided to reconsider what I was attempting and this is what I have come up with.

CREATE PROCEDURE some_proc_name
@.part = NULL,
@.sub_assembly = NULL
AS

CREATE TABLE #temp_table1
( t1_count int,
t2_count int,
part varchar(35),
subAssembly varchar(35)
)

CREATE TABLE #temp_table2
( t2_count int,
part varchar(35),
subAssembly(35)
)

IF(@.sub_assembly = NULL)
BEGIN
IF(@.part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
INSERT INTO #temp_table1(t1_count, part, subAssembly)
SELECT COUNT(RecordID), part, subAssembly
FROM some_table
WHERE part = @.part
GROUP BY part, subAssembly

INSERT INTO #temp_table2(t2_count, part, subAssembly)
SELECT COUNT(RecordID), part, subAssembly
FROM some_other_table
WHERE part = @.part
GROUP BY part, subAssembly
END
END
ELSE
BEGIN
INSERT INTO #temp_table1(t1_count, part, subAssembly)
SELECT COUNT(RecordID), part, subAssembly
FROM some_table
WHERE subAssembly = @.sub_assembly
GROUP BY part, subAssembly

INSERT INTO #temp_table2(t2_count, part, subAssembly)
SELECT COUNT(RecordID), part, subAssembly
FROM some other table
WHERE subAssembly = @.sub_assembly
GROUP BY part, subAssembly
END

UPDATE #temp_table1
SET t2_count =
(SELECT t2_count
FROM #temp_table2
WHERE part = #temp_table1.part)

SELECT *
FROM #temp_table1
WHERE t1_count <> t2_count
ORDER BY subAssembly, part

This seems to work fairly well. Thanks everyone for the input.