Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73

    Unanswered: 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.
    Code:
    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?

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    21
    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...
    wuadko

  3. #3
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    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 love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  4. #4
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and throwing a cursor into the mess too...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Just say no to cursors.
    I love deadlines. I like the whooshing sound they make as they fly by. Douglas Adams

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ok, I'll take a stab at this... I'd start with something like:
    Code:
    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 z
    It 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

  9. #9
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    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....

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    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.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by blindman
    ...but why the cursor?

    Even mentioning cursors on this forum starts raising concerns, because 9 time out of 10 (at least)
    More....9.99999
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2005
    Location
    Utah
    Posts
    73
    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.

    Code:
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •