Martin Reid
mwp.reid at qub.ac.uk
Wed Jan 28 11:14:42 CST 2009
John http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html Martin Martin WP Reid Information Services Queen's University Riddel Hall 185 Stranmillis Road Belfast BT9 5EE Tel : 02890974465 Email : mwp.reid at qub.ac.uk ________________________________________ From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby [jwcolby at colbyconsulting.com] Sent: 28 January 2009 17:08 To: Access Developers discussion and problem solving Subject: Re: [AccessD] Stored Procedure return value Asger, Thanks for the response. So that I can actually understand what I am doing, I have a couple of questions: > SET @sql = N'SELECT @PKID = min(PKID) FROM ' + @tblName; What does the N' do? > EXEC sp_executesql @sql, N'@PKID int OUTPUT', @PKID=@MinPKID OUTPUT; is this the equivalent of: DECLARE @PKID int OUTPUT EXEC sp_executesql @sql, @PKID Your code looks like perhaps it is dimensioning variables in the same line it is using them or something. Needless to say I am not there yet so understanding (and thus modifying to my needs) the code is difficult. John W. Colby www.ColbyConsulting.com Asger Blond wrote: > 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