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.
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
SELECT @selected FROM tbLogsPurchase_reqs ORDER BY cust_date
--the stored procedure is treating @selected as 1 value--
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?
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.
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.
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.