Link to home
Start Free TrialLog in
Avatar of ommer
ommer

asked on

how to send optional parameters to stored proc using sqlParameter?

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!
Avatar of jjaqua
jjaqua
Flag of United States of America image

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.
ASKER CERTIFIED SOLUTION
Avatar of KelvinY
KelvinY

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial