SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / Output Parameters / Latest Posts

RE: Output Parameters

Grant Fritchey — Sat, 27 Dec 2008 04:50:04 GMT

[quote][b]jpatevans4651 (12/24/2008)[/b][hr]Hi,Is there no limit to stored procedure parameters because when I use ado.net I get the following when trying to call a stored procedure with 12 parameters:Procedure or function GetItem has too many arguments specified.I know this isn't a .net board but figured others may have run into this.thanks.[/quote]There is a limit, I don't recall what it is off the top of my head, but it's available in Books Online. It's something like 256 I think, so 12 is not it. That error means the procedure had 11, or less, parameters and you passed it too many.

RE: Output Parameters

Matt Miller (#4) — Wed, 24 Dec 2008 09:15:23 GMT

[quote][b]Andrew la Grange (10/26/2007)[/b][hr]I've got to agree with Jereme. I'm currently working on a legacy system which is FULL of OUTPUT Parameters (which sadly more often than not contain nothing of use on a regular basis, but that is more an architectural error), and it is a major pain, especially when wanting to quickly run an SP and you don't care/need the output param vars. [/quote]I'm not quite sure I understand that one. I run SP's with output parms all of the time without even givng them a parm to give back to me when I don't care about them.To me - the main reason to use output parms is for when you want a scalar value out of the procedure. The execution status is for just that (whether the SP execute correctly or not) and it just doesn't make sense to make a recordset out of that. Of course - it becomes a different question when you want FIFTY scalar values......EDIT: never mind - just noticed how old that was....:)

RE: Output Parameters

jpatevans4651 — Wed, 24 Dec 2008 08:06:03 GMT

Hi,Is there no limit to stored procedure parameters because when I use ado.net I get the following when trying to call a stored procedure with 12 parameters:Procedure or function GetItem has too many arguments specified.I know this isn't a .net board but figured others may have run into this.thanks.

RE: Output Parameters

Jereme Guenther — Mon, 29 Oct 2007 13:58:03 GMT

I agree Doug, I have used them for that purpose as well.Nice tip about having to close the record set before accessing output parameters, I didn't know that.

RE: Output Parameters

Doug S. — Mon, 29 Oct 2007 12:35:49 GMT

for me, output parameters are most useful when your proc is an Insert on a table with an identity column. What you need is to pass back the Primary Key to the calling program. It works something like this-- Insert statement goes here with @TableId as output parametercreate procedure dbo.MyProc (@TableId integer output)as Insert into TableX (xxx,xxx,xxx)-- Put PK in @TableIdselect @TableId = SCOPE_IDENTITY()-- return @@error-- A common programming mistake is that you don't close the recordset In your calling program. You need to do this before you can reference the output parameter in your code. An example is provided here: http://msdn2.microsoft.com/en-us/library/aa905903(SQL.80).aspxDoug

RE: Output Parameters

Jereme Guenther — Fri, 26 Oct 2007 13:41:24 GMT

[quote][b]Sideout72 (10/26/2007)[/b][hr][quote][b]Jereme Guenther (10/25/2007)[/b][hr]So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance.[/quote]Think of using a stored procedure like you would use any fuction in traditional coding.You have parameters that could be "in", "out", or "in/out" for a function. Think of OUTPUT parameters in a stored procedure like "in/out" for a function.You also have a return value for a function. Think of a standard result set in a stored procedure like the return value for a function. Of course, that return value for a function could be multiple types - XML would be most like a standard SP result set.Hope that helps.[/quote]That does make sense. I find output parameters in functions to be cumbersome as well, though they have been useful on occasion.Also this comparison is great for the functional side of things, but implementation wise it is much more of a pain to implement output parameters in a sproc than it is in a .NET function.

RE: Output Parameters

Mark D Powell — Fri, 26 Oct 2007 12:35:08 GMT

It would have been nice if the start of the article contained a mention of to which versions of SQL Server the information is valid for. Many new DBA's may have to manage a shop where you can find verion 7.0, 2000, 2005, and soon 2008 all in use. Keeping straight what can be used where is a problem for new DBA's and part time SQL Server DBA's who work mainly with other databases.I also hate scrolling right to read text. Scrolling to read code isn't too bad but trying to read text in 4 word chunks is annoying. I do not mind scrolling up and down to read text but right, no.Any way fine article. Good blend on descriptive text and sample code.

RE: Output Parameters

John Masciantoni — Fri, 26 Oct 2007 11:13:06 GMT

Robert,Excellent article. I have found many uses for the SQL Output but have one small problem.Can anyone point me toward some example code that shows how to gather the output data when using VB.NET with late-binding in web applications. I use stored procs like crazy to return datasets but can't figure out how to get the OUTPUT items back into my program so I can do something with them. In the example below I would really like to just have the SP return an Output value of "true" or "false" and do away with all the extra check to see if a record came back or not.Thanks.John at Free DesignPublic Function CheckUserLogin(ByVal vLoginName As String) As Boolean Dim oSqlConn As Data.SqlClient.SqlConnection = SqlConnection() Try Dim oSqlCmdList As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("CheckUserLogin", oSqlConn) oSqlCmdList.CommandType = Data.CommandType.StoredProcedure Dim oSqlParm1 As New Data.SqlClient.SqlParameter("@UserLogin", vLoginName) oSqlCmdList.Parameters.Add(oSqlParm1) oSqlConn.Open() Dim oSQLDataAdapter As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter Dim oDataSet As Data.DataSet = New Data.DataSet oSQLDataAdapter.SelectCommand = oSqlCmdList oSQLDataAdapter.Fill(oDataSet, "ProcData") 'This checks for the existence of a login name If oDataSet.Tables("ProcData") Is DBNull.Value Then Return False Else Dim oDataView As Data.DataView = oDataSet.Tables("ProcData").DefaultView If oDataView.Count <= 0 Then Return False Else Return True End If End If Catch oErr As Exception WriteActivityLog(0, "SQL Error", "CheckUserLogin", oErr.Message) SendErrorEmail("Help@MyFreeDesign.com", "SQLProc-NTO CheckUserlogin", oErr.Message) Return False Finally If oSqlConn.State = Data.ConnectionState.Open Then oSqlConn.Close() End If End TryEnd Function

RE: Output Parameters

Jason-262847 — Fri, 26 Oct 2007 09:41:54 GMT

[quote][b]Jereme Guenther (10/25/2007)[/b][hr]So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance.[/quote]Think of using a stored procedure like you would use any fuction in traditional coding.You have parameters that could be "in", "out", or "in/out" for a function. Think of OUTPUT parameters in a stored procedure like "in/out" for a function.You also have a return value for a function. Think of a standard result set in a stored procedure like the return value for a function. Of course, that return value for a function could be multiple types - XML would be most like a standard SP result set.Hope that helps.

RE: Output Parameters

Jereme Guenther — Fri, 26 Oct 2007 09:25:13 GMT

"Output parameters make it easy to pass info between stored procedures."That sounds like what I missed, I didn't realize they could be used between sprocs. That is really good to know, thanks; I can definitely see where they would be handy in the database layer.

RE: Output Parameters

Matt Miller (#4) — Fri, 26 Oct 2007 08:39:10 GMT

A few things: - while .NET does make it relatively easy to go either way, it's not the only language. Most of the other solutions tend to have issues dealing with stored procedures returning more than one recordset, so if you need a recordset out of your stored proc AND something else, output parameters are going to become a convenient way to do that. - If you want something new from a stored proc, you're going to need to change your code no matter what. I'm not sure that any one method is better than any other? - Output parameters make it easy to pass info between stored procedures.

RE: Output Parameters

Mike Dougherty — Fri, 26 Oct 2007 08:23:43 GMT

We use JScript in the ASP environment, which to my knowledge does not support passing variables into the ADO object (like VBscript would allow) - so we must return a recordset in order to retrieve values from SQL. In the case of a simple insert, returning a recordset with the row Id is necessary. However, if that insert routine is called from another procedure the "row Id" recordset is probably not desired in the ADO results that the client (web server) is expecting. To manage multiple use of the insert procedure, I have been doing this:create procedure spExample @rowid int = null OUTPUT ,@returnRecordset bit = 0as begin/* body of procedure */set @rowid = scope_identity()if @returnRecordset = 1 select [rowid] = @rowidendThe Jscript caller over ADO uses: (needs a recordset) execute spExample @returnRecordset = 1Other stored procedures use: (do not pollute ADO results with extraneous recordset) execute spExample @rowid = @newPKID OUTPUT[Other likely parameters for an insert operation were omitted to simplify the example]

RE: Output Parameters

Grant Fritchey — Fri, 26 Oct 2007 07:21:38 GMT

[quote][b]Jereme Guenther (10/25/2007)[/b][hr]So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance. I don't see how they could be faster from the SQL side since the sproc needs to finish executing one way or the other.The only advantage I can see is that .NET (or whatever language you are using) doesn't need to instantiate another object (like the datareader or dataset) for such a small amount of data.Once I have gone down the path of OUTPUT parameters I have a couple extra places that code has to be updated every time I want to return an additional value. I have to pass in the new parameter, I have to add it to the sproc, I have to assign it in the sproc, and I have to retrieve it in .NET.If I am not using OUTPUT parameters then I simply add a new value to the select list in the sproc and add one more line in .NET to retrieve it.My final problem with OUTPUT parameters is that I can only return single values with them, so if I ever decide to change the data call to a two or three record result set I have to rewrite everything pertaining to the data retrieval call. So I can see in an area where speed to the last millisecond is absolutely essential they could be a really good thing, but other than that they seem like a bit of a pain.Am I missing something here? What are your thoughts on this?[/quote]I think you've covered everything correctly. The one advantage I see is that in instances where you know that you're only ever going to return very small sets of data, say an ID value from a single row insert procedure, then the amount of work done by the server is reduced because sending the output parameter requires a little less processing than marshalling a result set. You also save a touch on network traffic because the output parameter is going to be smaller.However, these are tiny, incremental, performance increases, realized after you've already squeezed everything you can out of the system. I wouldn't recommend implementing output parameters everywhere, but they do have a place and using them or not depends on the situation.

RE: Output Parameters

Jakub Poskrobko — Fri, 26 Oct 2007 05:00:06 GMT

We have implemented static, auto-generated C# DB Layer, that deals with all parameters in such way: for SP: exec mysp @par1 = @par1, @par2 = @par@we just call (generated) C# function: Exec_mysp (par1, par2);so, for out params i just write: int par1, par2; Exec_mysp (out par1, out par2). Autogenerating Exec_mysp function solves the problem if parameters list changes. In such solution - i always use out variables (even for select TOP 1 a,b,c from my_tab); and i think that works fine. Another point: not only store procs can be executed from C# (and other) using parameteres (and output params); it can be usual statement as well. kuba

RE: Output Parameters

Logan A — Fri, 26 Oct 2007 02:41:02 GMT

I don't tend to use them with .NET but do like using output parameters when having stored procedures run another (sub) stored procedure type module and a large record set is not required.Basically, in a way similar to creating / running a UDF but without the performance issues.UDF's of course are functionally simpler to code with and use... but its just not worth it.

RE: Output Parameters

Andrew la Grange — Fri, 26 Oct 2007 01:55:42 GMT

I've got to agree with Jereme. I'm currently working on a legacy system which is FULL of OUTPUT Parameters (which sadly more often than not contain nothing of use on a regular basis, but that is more an architectural error), and it is a major pain, especially when wanting to quickly run an SP and you don't care/need the output param vars. I read the article hoping there would be some sort of justification, or special case where OUTPUT params where the way to go... and I'm still left wondering. The only thing I can think of so far, is that its a legacy thing from when servers where a lot slower, and bandwidth a lot smaller than now. Returning a recordset (or more) seems so much simpler and more efficient. Yes, you might save some space on the transmission, and yes you wont have to instantiate an object on the client side, but considering the sheer amount of programming time you have to do to correctly handle OUTPUT params it almost doesn't seem worth the waste. As I see it, the PROs are: * Doesn't require recordset initialization* Light in transmission* Rigid Access to the SP [this is also a con]Cons: * Rigid Access to the SP* Code heavy on client and server side* More difficult to adjust dynamicallyI'm left to think that OUTPUT params are a hangover from a "quieter, gentler time" of SQL Server development, and the fact that extensive use of it (in my experience) seems to be limited to older systems (and older sql programmers) suggests this to true. But I'm really keen for someone to tell me how great OUTPUT params are, and how I'm missing the point; because if they are handy I'd love them to add them to my toolbelt. But at the moment they seem like a hammer for a screw.

RE: Output Parameters

VSVaidya — Thu, 25 Oct 2007 23:52:06 GMT

sunjiuludo not understandSELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]maybe you can explain a little bit.try these links talks about more on your stufff....http://www.sqlservercentral.com/articles/Stored+Procedures/outputparameters/1200/http://www.webtropy.com/articles/SQL.asp?SQL=sp_change_subscription_properties

RE: Output Parameters

Jereme Guenther — Thu, 25 Oct 2007 23:04:22 GMT

So what is the advantage of OUTPUT parameters over a standard result set? From my experience they are much more of a pain to deal with both in coding and in maintenance. I don't see how they could be faster from the SQL side since the sproc needs to finish executing one way or the other.The only advantage I can see is that .NET (or whatever language you are using) doesn't need to instantiate another object (like the datareader or dataset) for such a small amount of data.Once I have gone down the path of OUTPUT parameters I have a couple extra places that code has to be updated every time I want to return an additional value. I have to pass in the new parameter, I have to add it to the sproc, I have to assign it in the sproc, and I have to retrieve it in .NET.If I am not using OUTPUT parameters then I simply add a new value to the select list in the sproc and add one more line in .NET to retrieve it.My final problem with OUTPUT parameters is that I can only return single values with them, so if I ever decide to change the data call to a two or three record result set I have to rewrite everything pertaining to the data retrieval call. So I can see in an area where speed to the last millisecond is absolutely essential they could be a really good thing, but other than that they seem like a bit of a pain.Am I missing something here? What are your thoughts on this?

RE: Output Parameters

sunjiulu — Thu, 25 Nov 2004 06:52:00 GMT

do not understand

SELECT @intValue AS [@intValue], @strValue AS [@strValue], @bitValue AS [@bitValue]

maybe you can explain a little bit.

RE: Output Parameters

Andy Warren — Wed, 26 Nov 2003 07:52:00 GMT

Not much difference between VBS and ASP. As far as using output parameters, it works the same as plain ADO from VB...except!- You have to declare the constants in code or just use the numerical value for the consts. Not quite true for ASP, you can include a const file or set a reference (I think).- Nothing is strongly typed. You still build a parameters collection, just cant type it that way.If you have access to VB6, IMO you're better off to encapsulate as much of it as you can in a dll. It's compiled code, it's strongly typed, and you keep your data access out of the presentation layer.I've got some articles posted on ADO if you need some examples of that.Andyhttp://www.sqlservercentral.com/columnists/awarren/

RE: Output Parameters

Robert W Marda — Wed, 26 Nov 2003 07:34:00 GMT

Unfortunately, I don't know ASP nor js. As for VBScript, my focus has not been on getting data from SQL Server. This past week I began learning ColdFusion. I intend to learn ASP also since the environment I was given at work to learn ColdFusion also supports ASP, however I have not began yet with ASP.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: Output Parameters

JoeBelson — Tue, 25 Nov 2003 20:45:00 GMT

Wish you had included a few examples of retrieving the output values from ASP, VBS, js.

Output Parameters

Robert W Marda — Fri, 21 Nov 2003 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/outputparameters.asp