September 13, 2002 at 10:05 pm
Hi again,
My previous post wasn't totally accurate. I did some experimentation within SQL Server Query Analyzer and found that, even with the default of NULL, I had to provide a value when invoking the INSERT stored procedure. I had to actually provide "NULL" as a parameter; I could not leave any parameters blank as:
exec InsertRow 4, 3, 'NST', , , 7, 3
Instead, I had to use:
exec InsertRow 4, 3, 'NST', Null , Null, 7, 3
Effectively, this made the default a moot point and actually not needed.
Do I actually have to use the parameter names when invoking the stored procedure in order to get use of the defaults?
Mike
September 14, 2002 at 8:30 pm
Yes and No. As you mentioned, if you want to bypass a variable, you will have to state in you INSERT SQL which parameters you want to use such as: EXEC InsertRow @txtFirstName = 'something'.
However, you can also pass DEFAULT instead of Null if you don't know what the default value is: EXEC InsertRow 'M', DEFAULT, 'etc...'. This allows you to change the default value in the stored proc without changing your code.
I would like to point out an alternative. The best approach in calling a stored procedure is to create a command object in VB and set the parameters' values. Take a look at this code:
Dim oConn As New ADODB.Connection
Dim oCmd As New Command
' Connection string already set
oConn.Open sConn
' Create the command object and tell it you
' are calling a stored proc
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "InsertRow"
oCmd.ActiveConnection = oConn
' Now append mandatory parameter.
' For the mandatory params, I pass them right away
oCmd.Parameters.Append oCmd.CreateParameter("@sName", adVarChar, adParamInput, 30, Text1)
oCmd.Parameters.Append oCmd.CreateParameter("@Date", adDate, adParamInput, , Now())
' This is my optional param.
' Something in the field?
If Text2 <> "" Then
oCmd.Parameters.Append oCmd.CreateParameter("@sAddr", adVarChar, adParamInput, 30)
oCmd.Parameters("@sAddr").Value = Text2
End If
' Execute the command now...
oCmd.Execute
I hope this helps. I have migrated a few applications from in-line SQLs to using stored procs and found GREAT performance improvements every time.
Regards,
Herve
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
September 15, 2002 at 8:12 pm
Mike, I know exactly what you are attempting to do. I wrote a stored procedure to allow for dynamic inserts. Please email me if you want the code. Thanks
Brian
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply