Building SQL with T-SQL

Question

Building SQL with T-SQL

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

MikeAngelastro SSC Enthusiast Points: 170 More actions · Answer 1

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

Herve Roggero Ten Centuries Points: 1230 More actions · Answer 2

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

bgaines Newbie Points: 1 More actions · Answer 3

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

gainesme72@attbi.com