Darryl Collins
Darryl.Collins at coles.com.au
Tue Jan 27 19:00:28 CST 2009
Wow... Asger, that look most cool and useful, at least I think it does, I need to unpick that and get my head around it. :) cheers Darryl. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Asger Blond Sent: Wednesday, 28 January 2009 8:20 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Stored Procedure return value John, Have a look at sp_executesql, which allows you to send the value of an output parameter to a variable. You could create a sp like this: CREATE PROCEDURE usp_MinMaxPKID @tblName varchar(100) AS DECLARE @MinPKID int, @MaxPKID int, @sql nvarchar(100); SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName; EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MinPKID OUTPUT; SET @sql = N'SELECT @PKID = max(PKID) FROM ' + @tblName; EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MaxPKID OUTPUT; SELECT @MinPKID AS MinPKID, @MaxPKID AS MaxPKID; GO Then execute the sp like this: EXEC usp_MinMaxPKID 'MyTable'; GO HTH Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 27. januar 2009 19:39 Til: Access Developers discussion and problem solving Emne: Re: [AccessD] Stored Procedure return value I am not going to get into a "dynamic SQL" debate. Simply understand that I run SQL Servers that have exactly and only one person accessing them, ME. I have literally many dozens of databases, each with dozens of queries and tables. I am not going to edit the stored procedures in each database I create in order to avoid an injection attack that will never come. In my case it does initially complicate what I want to do but it simplifies the big picture. And once I learn the technique, whatever it might be, then it will no longer complicate my life. I have found an article about exactly what I am trying to do, but of course it is just complicated enough that I have to study it very carefully in order to figure out how the heck they are doing it. Thanks for your example, I will work with it as well. John W. Colby www.ColbyConsulting.com Arthur Fuller wrote: > 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 >> -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com This email and any attachments may contain privileged and confidential information and are intended for the named addressee only. If you have received this e-mail in error, please notify the sender and delete this e-mail immediately. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. It is your responsibility to check this e-mail and any attachments for viruses. No warranty is made that this material is free from computer virus or any other defect or error. Any loss/damage incurred by using this material is not the sender's responsibility. The sender's entire liability will be limited to resupplying the material.