Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: How To Pass Table_name And Col_name As Parameters To Stored Procedure

    HI,

    I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

    CREATE PROCEDURE MY_PROC
    AS
    DECLARE FETCHER CURSOR SCROLL
    FOR
    SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID
    FROM CUSTOMER A
    INNER JOIN CUSTOMERPREFERENCE B
    ON A.INTCUSTOMERID = B.INTCUSTOMERID
    OPEN FETCHER

    WHILE @@FETCH_STATUS = 0
    FETCH NEXT FROM FETCHER
    CLOSE FETCHER


    WHICH IS WORKING FINE WHEN I EXECUTE :-

    EXEC MY_PROC

    BUT I WANT TO PASS BOTH TABLE_NAMES AND COL_NAMES AS PARAMETERS :-

    LIKE :-

    EXEC MY_PROC [TABLE_NAME],[COL_NAME{1.....n}]

    SO THAT I CAN CHANGE BOTH THE TABLE_NAME AND COL_NAME
    HOW TO DO THIS ?

    tHIS IS ONE MORE PROBLEM THAT I AM FACING.PLS HELP


    tHANKS.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sorry, but any time I see something like this I first have to ask what you plan to do with the data and why you feel you need to use a cursor.

    What happens after FETCH NEXT FROM FETCHER?
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2004
    Posts
    49
    Hi,
    I See, Why You Are Asking This Question :-

    But The Thing Is I Am Using It To Retrieve Data One Row At A Time
    To Chk It Against Some Given Parameters And Then Manipulate The Data According To It.

    Though, I Have Not Written Those Parameters Out Here,it's Huge

    And The Data Is Dynamic :-

    So I Have To Use Cursors To Validate The Data Against Those Parameters Row Wise.

    As Most Of The Procedures Takes Place Similarly Against Different Set Of Data's Stored In Different Tables :-

    What I Require Is To Send Table_name And Col_name As Parameters To The Cursor So That I Don't Have To Run Different Procedures .. But Only Have To Pass Different Parameters.

    Thanks.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Doing anything "one row at a time" really ought to be done on a client (possibly a N-tier server), not on SQL Server. Based on the way that SQL Server was designed, this is a reciepe for poor performance for both this process as well as everything else on your server.

    -PatP

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "But The Thing Is I Am Using It To Retrieve Data One Row At A Time
    To Chk It Against Some Given Parameters And Then Manipulate The Data According To It."

    This does not necessitate the use of a cursor. A cursor might be appropriate if the actions you perform on records X depends upon the actions you performed on a prior record. But if the actions you perform on each record are independent of the actions you perform on other records, then you are better off running multiple set-based operations.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    it smells like bad application design, not necessarily a bad database design. consult with your dba to see if he (or she) may suggest a different approach to "displaying data". and definitely loose the cursor.

  7. #7
    Join Date
    Apr 2004
    Posts
    49

    Question

    HI,

    THANKS FOR THE REPLIES,THOUGH I AM A RELATIVELY NEW-COMER,I APPRECIATE THE TIME ALL OF YOU HAVE TAKEN IN TO REPLY.....BUT.....
    THE PROBLEM STILL PERSISTS HAS I STILL BEEN UNABLE TO FIGURE IT OUT.

    COULD YOU GUYS PLS PROVIDE ME AN ANSWER TO IT :-

    FIRST OF ALL,IT MIGHT BE A BAD APPLICATION DESIGN.
    SECOND , MAYBE IT DOES NOT OPTIMIZE SQL PERFORMANCE.
    AND THIRD , "A cursor might be appropriate if the actions you perform on records X depends upon the actions you performed on a prior record"

    YES IT'S THE SAME WAY CAUSE THE OVERALL RESULTS REFLECTS IT.

    THE PROBLEM NOW IS :- FORGET THE SQL PERFORMANCE OPTIMIZATION OR
    "BAD APPLICATION DESIGN"

    PLS GIVE ME A SOLUTION TO GET AROUND THIS :-
    CAUSE IN THE LONG-TERM WE COULD THINK OF SOMETHING ,BUT IN THE SHORT TERM I NEED TO USE A CURSOR AND PASS THE PARAMETERS TO IT.

    I KNOW THIS COULD BE DONE BY GETTING AROUND IT,JUST DON'T KNOW HOW.

    PLS GIVE ME A SOLUTION.

    THANKS

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Even if the answer were easy, it would still be wrong.

    You could use dynamic SQL to do what you want, but it is still fundamentally wrong. Going down that path will simply get you a bit further, then leave you stranded with no real solution available. Giving you the code to make that happen isn't helping you, even if it does what you want right now.

    You really need to revisit the underlying problem and find a solution that will work in the long term instead of trying to bludgeon this into working the way that you imagine it right now.

    -PatP

  9. #9
    Join Date
    Apr 2004
    Posts
    49
    HI,

    I UNDERSTAND WHAT YOU ARE TELLING ME ,BUT THE PROBLEM LIES IN THE FACT THAT THE FINAL REPORT HAD TO BE GENERATED BY LAST WEEKEND.AND I AM ALREADY LATE TO :-

    "You really need to revisit the underlying problem and find a solution that will work in the long term instead of trying to bludgeon this into working the way that you imagine it right now."

    RIGHT NOW, MY IMMEDIATE CONCERN IS TO GET THE REPORTS DONE.....
    WHICH IN TURN IS REALLLY GONNA HELP ME OUT..THOUGH FUNDAMENTALLY IT MIGHT BE WRONG.

    YES AFTER THE REPORTS ARE DONE I CAN LOOK INTO THE UNDERLYING PROBLEM AND GET A SOLUTION.BUT RIGHT NOW THERE IS NO TIME FOR IT.

    SO IF YOU HAVE ANY SOLUTION. PLS GIVE IT TO ME.

    THANKS..

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please remember that I've warned you that this is a bad idea, and that you have asked me again for the answer. This is emphatically NOT a good idea!
    Code:
    CREATE PROCEDURE myProc
       @cTableList VARCHAR(200)
    ,  @cColumnList VARCHAR(200)
    AS
    
    EXECUTE ('DECLARE FETCHER CURSOR FOR SELECT '
    +  @cColumnList + ' FROM ' + @cTableList)
    
    OPEN FETCHER
    --  and so forth
    You then call this using something like
    Code:
    EXECUTE myProc
       'CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID'
    ,  'A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID'
    This is still bad code. It will load your server down for no good reason, and will probably lead you to another problem that has no solution.

    -PatP

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, ok, ok. I'll try to give you some help here.

    Can you use dynamic SQL to select your data into a temp table, and then run your cursor from the temp table?
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I get this image of a guy on a scaffold, yelling "More rope" to the crowd around him!

    -PatP

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It sounds like he already has the noose around his neck, and just wants enough rope to get his feet to the ground safely.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29
    Hi all,

    I followed this discussion and wondered what then should be the correct solution to this problem? Anyone?

    Greetz,
    DePrins,

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by deprins1968
    I followed this discussion and wondered what then should be the correct solution to this problem? Anyone?
    The problem is that they are trying to apply a row-oriented solution, much like you might use with Oracle. That is a good solution for a client or N-Tier solution, but it is a very bad choice for the database server.

    To get good performance from the current generation of database engines, you need to think in sets. That means that you need to engineer your solution around groups of rows, not around single rows.

    The reliance on cursors and row oriented thinking is the "road to ruin" as far as I'm concerned. It makes good sense to work "row oriented" with tools that have very old (unit-record) oriented architectures, but it is a very poor choice for forward looking tools.

    -PatP
    Last edited by Pat Phelan; 05-03-04 at 12:20.

Posting Permissions

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