Solved

how to send optional parameters to stored proc using sqlParameter?

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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