jwcolby
jwcolby at colbyconsulting.com
Tue Jan 27 12:39:11 CST 2009
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 >>