Asger Blond
ab-mi at post3.tele.dk
Thu Jan 29 11:07:50 CST 2009
John, I put a _X after the variable name only to make clear that this variable is quite *different* from the variable @MinPK you declare at the beginning of your sp. I could have used any name. Yes, actually I could have used the name @MinPK, but it still would be a variable *different* from the @MinPK you declare at the beginning. OK, let's use a different name, e.g. @internalMinPK, and have a look at the code: CREATE PROCEDURE usp_MinMaxPKID @TblName varchar(50) AS DECLARE @MinPK int, @SQL nvarchar (4000) . . set @SQL = N'SELECT @internalMinPK = MIN(PKID) FROM ' + @TblName exec sp_executesql @SQL, N'@internalMinPK int OUTPUT', @internalMinPK = @MinPK OUTPUT . . So, what's going on here? You are quite right: In the first argument for sp_executesql you are telling sp_executesql that you want to use @internalMinPK inside of its code. And in the second argument that this variable is for an int data type and that it should return a value. The last argument is: @internalMinPK = @MinPK OUTPUT This part tells sp_executesql to store the value of @internalMinPK into your variable @MinPK. I admit the syntax may be confusing (at least I was quite so first time I saw it): When using an equal sign you normally store the value on the right side to the left side - here it is the other way round... My understanding (I may be wrong, anyone please correct me if so!) is that this "turnaround" is exactly what OUTPUT means. So I would read the last argument this way: "Take the value of @internalMinPK and output it to @MinPK". HTH Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af jwcolby Sendt: 29. januar 2009 15:58 Til: Access Developers discussion and problem solving Emne: Re: [AccessD] Stored Procedure return value Asger, Thanks for your responses. This worked for me as well! <Rant Mode> My problem in all of this is that I very much a novice at SQL Server. I have been forced to dig in but, like Access, there is just so much to know. If I sit here just "experimenting" trying this and that I get nowhere. There is no "debugger" for SPs so it is "try something, run and see". Worse yet the totality of the feedback is "error near XXX in line YYY", not exactly stellar help. It would help me and anyone else following this to know the WHY. "Change to the following" does in fact make it work, it just doesn't help me in making the NEXT one work without coming back to you. WHY do I have to put an _X in there? What exactly is this thing DOING? Let's break this thing down a little bit. > set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName I have been doing these kinds of statements, except that I have been just doing an EXEC(@SQL). When I do it this way, I do NOT use the @ sign in front of the MinPK, and the results get placed into the result tab. Now you tell me to place an _X after the variable name, but I do not have a variable named _X. What is this @MinPK_X, where does it exist? So I am already off in my head trying to discover what is going on in this line of code, when the answer is in the next line of code. > exec sp_executesql @sql, N'@MinPK_X int OUTPUT', @MinPK_X=@MinPK OUTPUT Now we see this _X variable again, in a "definition" kind of statement. So maybe (I am guessing here) I am telling sp_executesql to create @MinPK_X inside of its code and that it will be a variable that returns a value. >@MinPK_X=@MinPK OUTPUT Now (maybe, I am guessing here) I have to pass in my variable (@MinPK) to the variable that sp_executesql has just been told to set up for itself (@MinPK_X). I still don't have a clue what the final OUTPUT does. I defined N'@MinPK_X int OUTPUT', why the final OUTPUT? This kind of an explanation for code goes a long way towards helping me do it for myself in the future. When I read your code, I don't have this "explanation" dialog going on in my head, I have more of a WTF? dialog going on. Step back for a moment, forget what you already know about SQL Server and sp_executesql, and then look at these two lines of code: set @SQL = N'SELECT @MinPK_X = MIN(PKID) FROM ' + @TblName exec sp_executesql @sql, N'@MinPK_X int OUTPUT', @MinPK_X=@MinPK OUTPUT Pretty damned cryptic without a whole ton of pre-existing knowledge. Now I am all about BOL (NOT!) and I in fact went out and read the help on sp_executesql, but one critical thing to understand about BOL is that it is all about context. "To understand this, you already have to understand that". So to say "Go read BOL" is less than helpful. I have spent hours chasing my tail in BOL... OK now go read THIS... ok now to understand that go read this other... ok now to understand that... I am a reasonably smart guy, and if I can read an English language explanation of the WHY then I can pretty much make it work again, and after a while I am comfortable with it. <\Rant Mode> And I still don't understand the final OUTPUT. Asger, I am in fact most appreciative of your help on this. While I still am not sure of the "why it works", I at least have a solution that I can go use this time. I may in fact kinda sorta understand enough to make it work in a different circumstance. Thanks for your assistance. John W. Colby www.ColbyConsulting.com Asger Blond wrote: > John, > > Change the two statements: > > set @SQL = N'SELECT @MinPK = MIN(PKID) FROM ' + @TblName > exec sp_executesql @sql, N'@MinPK int', @MinPK > > To: > > set @SQL = N'SELECT @MinPK_X = MIN(PKID) FROM ' + @TblName > exec sp_executesql @sql, N'@MinPK_X int OUTPUT', @MinPK_X=@MinPK OUTPUT > > > Works for me > > Asger -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com