Solved

how to send optional parameters to stored proc using sqlParameter?

Posted on 2006-07-19
2
526 Views
Last Modified: 2008-01-09
I have this stored proc that will take optional parameters:
USE Pubs
GO

CREATE PROCEDURE dbo.GetAuthors2
    @lastName VARCHAR(32) = NULL,
    @firstName VARCHAR(32) = NULL
AS
BEGIN
    SET NOCOUNT ON
 
    SELECT * FROM Authors
        WHERE AU_LName LIKE COALESCE(@lastName, '%')
        and au_fname like coalesce(@firstname,'%')
END
GO

How do I send in a null value for the optional parameter in the following setting?

Dim arParms() As SqlParameter = New SqlParameter(1) {}
        arParms(0) = New SqlParameter("@lastname", SqlDbType.VarChar)
        arParms(0).Value = Null '???
        arParms(1) = New SqlParameter("@firstname", SqlDbType.VarChar)
        arParms(1).Value = "Anne"
 ds = SqlHelper.ExecuteDataset(cn_str_Pubs_Dev, CommandType.StoredProcedure, "GetAuthors2", arParms)

The SqlHelper is from MS Data Access Application Block.

Thanks!
0
Comment
Question by:ommer
2 Comments
 
LVL 5

Expert Comment

by:jjaqua
ID: 17142188
I'm pretty sure you don't have to send the parameter at all. It will be Null in the sproc if nothing is sent for that parameter.
0
 
LVL 8

Accepted Solution

by:
KelvinY earned 250 total points
ID: 17142207
Hi ommer,
Try

  arParms(0).Value = System.DBNull.Value

Regards
  Kelvin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now