Solved

how to send optional parameters to stored proc using sqlParameter?

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 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

17 Experts available now in Live!

Get 1:1 Help Now