Arthur Fuller
fuller.artful at gmail.com
Tue Jan 27 09:29:31 CST 2009
Hi John, I must confess that I am not a fan of dynamic SQL, and that in your case it complicates things. Normally (e.g. static SQL) one would write something like this: SET @minID = (SELECT Min(PKID) FROM myTable) and similarly for @maxID I haven't actually tried doing this in dynamic SQL but I think the same thing should work from there too. The trick is the SET part of the statement. hth, Arthur On Tue, Jan 27, 2009 at 8:50 AM, jwcolby <jwcolby at colbyconsulting.com>wrote: > Arthur, > > What I still don't understand is how I get the data into the > variables to return INSIDE of the stored procedure. > > > For example, I might have a table with the PKID starts at > ~13 million and go to 87 million. My external process can > only process 1 million records maximum so I need to grab 1 > million record chunks of data. > > I need to get the min(PKID) and max(PKID) and I need to > return these to code out in Access so that I can then "get > the next million records" based on the PKID. I want to do a > bulk export starting at 13 million to 14 million, then when > that works, 14 to 15 million etc. > > I have the SP written and tested that does that bulk export, > now all I need is the min(PKID) and Max(PKID) so I can start > carving up the table. > > I have written a usp_MinMaxPKID @tblName varchar, @MinPKID > int output, @MaxPKID int output > > So I have a SP with the values passed in and declared OUTPUT > (return value to calling procedure). In the past I have > written dynamic code something like: > > declare @SQL = 'SELECT Min(PKID) as MinPKID, Max(PKID) as > MaxPKID from dbo.' + @tblName. > > exec(@sql) > > This had the effect of getting the min and max from the > table passed into @tblName, but it places the results into > the results pane in the query window when I execute the SP. > > Now I need to somehow place the results into the @MinPKID > and @MaxPKID. It is this piece that I am "missing". > > Any help (from anyone) on how to do this would be much > appreciated. > > John W. Colby > www.ColbyConsulting.com <http://www.colbyconsulting.com/> > > > Arthur Fuller wrote: > >>From the FE: > > > > declare a variable called intRowCount and set its value to zero. > > > >>From the BE's proc: > > > > Declare a parm called @intRowCount OUTPUT > > > > Execute the proc, passing your parm in. > > > > Interrogate the variable intRowCount upon return to your external code. > > > > That should do it. > > > > If this is unclear, I could whip up a toy program for you, but I think > that > > this ought to be clear enough for you to proceed. If not, say so and I > shall > > supply a toy program that demo's this approach. > > > > A. > > > > On Fri, Jan 23, 2009 at 5:35 PM, jwcolby <jwcolby at colbyconsulting.com > >wrote: > > > >> can the sp be tested from the query window in SQL Server. > >> IOW, I open a query, EXEC the sp_CountRec and pass in ... what? > >> > >> John W. Colby > >> www.ColbyConsulting.com <http://www.colbyconsulting.com/> < > http://www.colbyconsulting.com/> > >> > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >