Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
John T.
Posting Yak Master
112 Posts |
 Posted - 2003-04-18 : 22:56:04
|
| I am having a problem getting a good handle on stored procedures. I don't seem to have any problem with all input values. I did one today that simply returned COUNT function value. But now I am trying to send 4 values into the SPROC. Two that are needed to do the query and the other two are for a return. I have been searching quite a bit for code samples. I will ask a dumb question maybe. But am I doing something wrong trying to combine the code below with input and output values? And another dumb question. What is the difference between an output and return value? Or for that matter, input-output vs. the aforementioned two. Somebody hopefully can turn the light on for me.Thanks.By the way, the error message I get with the code below is a runtime telling me that "@GNo was defined as OUTPUT but the actual parameter not declared OUTPUT." <code>Sub MainCheck(ByVal GNo As Short, ByVal GType As String, ByVal GInt As Short, ByVal TypeInt As Short) Dim connectionString As String = "string" Dim sqlConnection AS SqlConnection = New SqlConnection(connectionString) sqlConnection.Open() Dim cmd As New SqlCommand("MainCheckProc", sqlConnection) cmd.CommandType = CommandType.StoredProcedure Dim param1 As New SqlParameter("@GNo", SqlDBType.SmallInt) param1.Direction = ParameterDirection.Input param1.value = GNo cmd.Parameters.Add(param1) Dim param2 As New SqlParameter("@GType", SqlDBType.VarChar) param2.Direction = ParameterDirection.Input param2.value = GType cmd.Parameters.Add(param2) Dim param3 As New SqlParameter("@GInt", SqlDBType.SmallInt) param3.Direction = ParameterDirection.Input param3.value = GNo cmd.Parameters.Add(param3) Dim param4 As New SqlParameter("@TypeInt", SqlDBType.SmallInt) param4.Direction = ParameterDirection.Input param4.value = GNo cmd.Parameters.Add(param4) cmd.ExecuteNonQuery() sqlConnection.Close() End Sub<code>The procedure is such : CREATE PROCEDURE MainChkProc @GNo SMALLINT, @GType VarChar(50), @GInt SMALLINT OUTPUT, @TypeInt SMALLINT OUTPUT AS SELECT @GType = COUNT(fld1) FROM TESTTABLE WHERE (fld1) = @GNo AND (fld2) = @GType SELECT @TypeInt = COUNT(fld1) FROM TESTTABLE WHERE (fld1) = @GNo AND (fld6) > GETDATE() |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-04-18 : 23:07:36
|
In your vb code, all your parameters are defined explicitly as Input. You need to change them to output to match your proc.Return values are generally used for returning an error code, not unlike a funtion that returns a number, and are a little trickier to use in ADO(X). The difference between INPUT and OUTPUT parameters is more akin to ByVal and ByRef arguments in a VB function. An INPUT parameter gets passed into the procedure. An OUTPUT parameter is passed in (usually NULL though) and you get the value back from the procedure, kinda like a ByRef argument (this will get me killed by some people here  ).DamianEdited by - merkin on 04/18/2003 23:13:39 |
 |
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-04-18 : 23:46:14
|
| I apologize. The last 2 ParameterDirections are output. A typo on my part. Is this allowed? Input and output direction values together? Sorry again for the typo. My bad indeed. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-19 : 08:07:49
|
| >> this will get me killed by some people hereWhere's my wooden stake.The error you are getting says that the first paramjeter is being passed as output.I normally define output parameters as inputoutput to point out there is no such thing as output only.You do not need to name the parameters (I leave them blank) but it's up to you. .net does require the names to be correct unfortunately.You should also give a length to the varchar.I use a database access class to do all the ado specific stuff.You could call an object method which returns a parameter so that all your parameter additions are a single line and you can change them all in a single place if necessary.Also put the exec into a class so that you can put in logging for all database access in a single place.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
John T.
Posting Yak Master
112 Posts |
Posted - 2003-04-19 : 10:36:29
|
| Thanks very much. Got it working. |
|
|
|
|
|
|
|
|
Stored Procedures question