Solved

how to send optional parameters to stored proc using sqlParameter?

Posted on 2006-07-19
2
527 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 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