| Author |
Topic |
|
jp2code
Posting Yak Master
175 Posts |
 Posted - 2008-07-22 : 15:08:41
|
| Is it possible to run a query on a data table that is the result of a stored procedure? If so, how?My stored procedure name is called GetDataByDates and it takes two dates (startDate and endDate). What I would like to be able to do is do a search on this returned data table using one of the fields that are returned (like Operator_ID).Here is my example that does not work:select * fromexecute GetDataByDates '06/01/2008', '07/15/2008' Awhere (A.Operator_ID like '158A755')I would like to make this work because it would mean that my Visual Studio applications would be able to reuse many of our existing stored procedures without writing new procedures. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-22 : 16:04:44
|
| Insert the results of the stored procedure into a temporary table likeINSERT INTO #Tempsp_executesql GetDateByDates...Then issue your select against the Temp table.By the way, why can't your Visual Studio applications simply execute the existing stored procedure, possibly return it to a DataTable or some such construct and then work on it from there?---------------------------EmeraldCityDomains.com |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-07-22 : 16:19:05
|
| or maybe add another parameter to the stored procedure: @Operator_idBe One with the OptimizerTG |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-07-22 : 16:43:16
|
| First, AjarnMark:I am filling these into DataTables, but these are then bound to a DataGridView. By creating the DataTable as an already filtered creature, the DataGridView has what it needs. My other option would be to create a second table from the first, and use that data to bind to the DataGridView.TG: We've got a lot of stored procedures now, and Management would like us to use what is there if at all possible before we submit another for review. Go-Go Corporate Sector!It is irritating how this board requires me to log in before every post. Is there a way to make that feature go away? I have "Save Password" checked, but I still have to insert it and my UserName every time I post. Strange. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
EugeneLim11
Posting Yak Master
167 Posts |
Posted - 2008-07-22 : 23:48:16
|
quote:
Originally posted by jp2code
First, AjarnMark:I am filling these into DataTables, but these are then bound to a DataGridView. By creating the DataTable as an already filtered creature, the DataGridView has what it needs. My other option would be to create a second table from the first, and use that data to bind to the DataGridView.TG: We've got a lot of stored procedures now, and Management would like us to use what is there if at all possible before we submit another for review. Go-Go Corporate Sector!
You mean you have store procedures for every possible way of filtering data? This kinda sucks - it does not faciliate reuse then. Here is an idea for reusing your store procedures. 1. Get all information that you will be displaying into a data table (dt) (unfiltered). 2. use dt.Defaultview.RowFilter to filter the data to display what you need. 3. Set the dataGrid.Datasource (or GridView) etc, to the dt.DefaultView. 4. bind the dataGrid.This works much better, less stored procedures, less code and easier to maintain. Your manager is happy with less stored procedures, your programmers also happy with less creation of store procedures for every scenario etc. I can bet that if the database requires some change, you are going to have to look through your store procedures one by one to see if it is impacted. Why not just have one store procedure for each screen? Store procedures should be reused - in fact I resuse the same store procedures frequently and then filter the display to display only the relevant fields. If the store procedure frequently have the same logic, e.g. select xxx from table abc, xyz where abc.Id= xyz.id, it is better to create a view to ease your pain of writing store procedure everytime, and select data from the view. I found that when I keep it simple, it is the most effective way to troubleshoot or manage changes. :) Just my two cents worth.Eugene |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-23 : 01:23:54
|
Eugene hit on what I was going to recommend for filtering on the .NET side of life.As for:quote:
Originally posted by jp2codeIt is irritating how this board requires me to log in before every post. Is there a way to make that feature go away? I have "Save Password" checked, but I still have to insert it and my UserName every time I post. Strange.
If you don't actually Logout then the board recognizes you the next time you come in, as long as it was within a reasonable number of weeks.---------------------------EmeraldCityDomains.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-23 : 04:20:55
|
quote:
Originally posted by jp2code
Is it possible to run a query on a data table that is the result of a stored procedure? If so, how?My stored procedure name is called GetDataByDates and it takes two dates (startDate and endDate). What I would like to be able to do is do a search on this returned data table using one of the fields that are returned (like Operator_ID).Here is my example that does not work:select * fromexecute GetDataByDates '06/01/2008', '07/15/2008' Awhere (A.Operator_ID like '158A755')I would like to make this work because it would mean that my Visual Studio applications would be able to reuse many of our existing stored procedures without writing new procedures.
you can use OPENROWSET to get fields from procedure selectively. refer link belowhttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-07-23 : 08:45:29
|
quote:
TG: We've got a lot of stored procedures now, and Management would like us to use what is there if at all possible before we submit another for review.
I'm not saying add a new procedure, I'm saying add a new parameter to the existing SP. Its more efficient to filter the data at the database then sending it all to the client and filtering it there.Be One with the OptimizerTG |
|
|
|
jp2code
Posting Yak Master
175 Posts |
Posted - 2008-07-23 : 09:51:48
|
quote:
Originally posted by EugeneLim111. Get all information that you will be displaying into a data table (dt) (unfiltered). 2. use dt.Defaultview.RowFilter to filter the data to display what you need. 3. Set the dataGrid.Datasource (or GridView) etc, to the dt.DefaultView. 4. bind the dataGrid.
Thanks Eugene! That's an excellent idea. I knew how to get data into a DataTable or a DataView, but I never understood the relationship between a DataTable and a DataView until you pointed it out in Step 2 above.Ok, I have 2 handfuls of ideas to work with now. Let me get to coding...Thanks to all!~Joe |
|
|
|
|
Select from Procedure