October 26, 2007 at 11:13 am
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 Design
Public 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 Try
End Function
October 26, 2007 at 12:35 pm
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.
October 26, 2007 at 1:41 pm
Sideout72 (10/26/2007)
Jereme Guenther (10/25/2007)
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.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.
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.
October 29, 2007 at 12:35 pm
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 parameter
create procedure dbo.MyProc (@TableId integer output)
as
Insert into TableX (xxx,xxx,xxx)
-- Put PK in @TableId
select @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).aspx
Doug
October 29, 2007 at 1:58 pm
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.
December 24, 2008 at 8:06 am
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.
December 24, 2008 at 9:15 am
Andrew la Grange (10/26/2007)
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'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....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 27, 2008 at 4:50 am
jpatevans4651 (12/24/2008)
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.
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.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply