Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21

    Unanswered: Stored Procedures and Select Statments

    I am using VB.Net, and I want to create a stored procedure that returns a varying number of columns. I am allowing my users to select which columns they want to see, and I need to send this "variable" (or parameter) to the stored procedure and use it in the "Select" section.

    I was able to do this using strickly VB.Net, but our DBAdmin said that I had to use stored procedures, and now I can't get it to work.

    VB.Net Code:

    strSQL = "SELECT '" & lbl1.Text & "' FROM tbLogsPurchase_reqs ORDER BY cust_date"

    --lbl1.Text has the column names separated by a comma--

    I have tried this in the Stored procedures:

    ALTER PROCEDURE dbo.tbLogsPurchase_reqs_Select

    (
    @selected varchar(300)
    )

    AS
    SELECT @selected FROM tbLogsPurchase_reqs ORDER BY cust_date
    RETURN

    --the stored procedure is treating @selected as 1 value--

    Please Help!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to use dynamic sql (although I don't recommend it, and your dba will probably hate me)

    Code:
    ALTER PROCEDURE dbo.tbLogsPurchase_reqs_Select
    
    (
    @selected varchar(300)
    )
    
    AS
    DECLARE @SQL varchar(8000)
    
    SELECT @SQL = 'SELECT ' + @selected + ' FROM tbLogsPurchase_reqs ORDER BY cust_date'
    
    EXEC(@SQL)
    
    RETURN
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, your DBA will hate you.

    If choosing which columns to display isn't a function of the interface, I don't know what is. As an interface developer, you should be handling this.

    blindman

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's like letting the genie out of the bottle...

    If SQL is like the force...Dynamic SQL is the dark side...

    Mind you what you've learned...save you it can...
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm confused. Are DBAs the Jedis or the Stormtroopers? And why do I end up feeling like an Ewok at the end of the day?

    Darth Blindman

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    let us know what your website is, we'll break in and you'll get a better dba

  7. #7
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21
    Thanks Brett Kaiser. I am obviously new to SQL, so I don't know all the proper techniques, but I'll give this a shot.

    Just for kicks, would the only other option be declaring a parameter for each column, and then passing in null values for the ones that are not needed. Are users just not supposed to have this functionality?

    Oh, and go Jedis!

  8. #8
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21
    BTW,

    I'm having another issue, and I was wondering if it was from my VB.Net code, the stored procedures, or what.

    My DBA says that all of the system stored procueders are showing up with their type as "User". He said he hadn't seen this before, and was wondering what I was doing? :-)

    Being new, I wasn't sure what the problem was. So, if anybody knows, I'd love to hear. Thanks!

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In the master database? I can't think of anything you could have done to cause this. Maybe he needs to check the integrity of all the databases, especially master. It sounds like some table or index may be corrupted.

    blindman

  10. #10
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21
    Well, the problem is only occuring in the one database that I am working on, not in any others (at least not that I know of). The only thing I did that I found out later was wrong, was not using stored procedures. I was using the insert, update, and select statements directly in my VB code. Also, I was running these commands under my user name and password. we have since set up a group that has access to the new stored procedures.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    I'm confused. Are DBAs the Jedis or the Stormtroopers?
    Everyone has access to the force (SQL)...it's how you use it...

    Originally posted by blindman
    And why do I end up feeling like an Ewok at the end of the day?
    Those ewoks no how to party...

    But I guess the point is...how many columns are available for selection?

    You'd be better off selecting all the columns (or have a series of selects with reduced lists) and only display what they want...

    Instead of using dynamic (the dark side) SQL...

    You need to use it as a LAST resort....

    Too many developers out there signin...

    (everybody now)


    "Nooobody knows the trouble I've seen..."
    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.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    From Books Online:

    "System stored procedures are created and stored in the master database and have the sp_ prefix. "

    You aren't in the master database, are you?

    blindman

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Perhaps an explanation of why dynamic sql is bad, at least for procedures that are available to users.

    You submit a string of column names to a parameter called @selected, and your procedure executes this statement:

    SELECT @selected FROM tbLogsPurchase_reqs ORDER BY cust_date

    You are expecting @selected to be a string such as "column1, column2, column 4", but what if someone malicious submitted this string instead:

    "column1 from tblLogsPurchase_reqs delete * from AnImportantTable select column1"

    Your server then dutifully executes the concatenated code:

    SELECT column1 from tblLogsPurchase_reqs
    delete * from AnImportantTable
    select column1 FROM tbLogsPurchase_reqs ORDER BY cust_date

    NOW do you see why DBAs don't like procedures that execute dynamic sql?

  14. #14
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21
    no, I'm not in the master db, and these stored procedures don't have an sp_ prefix, they all have dt_. So maybe I was slightly confused about what our DBA was saying.

    Here are some of the stored procedures. I didn't create them, so if they are not system sp's, then what are they:

    dt_addtosourcecontrol
    dt_addtosourcecontrol_u
    dt_adduserobject
    dt_adduserobject_vcs
    .
    .
    .
    dt_whocheckedout
    dt_whocheckedout_u

    there are like 30 of them. Thanks for all the help!

  15. #15
    Join Date
    Oct 2003
    Location
    Maryland
    Posts
    21
    Sure Darth blindman, that makes sense. In my case, I am the only developer that would ever be writing code for this db, and the web end is only accessible through our intranet. So, I don't think I would have to worry as much, but I can definitely understand that this should not be a solution that I rely on.

Posting Permissions

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