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(c n_str_Pubs _Dev, CommandType.StoredProcedur e, "GetAuthors2", arParms)
The SqlHelper is from MS Data Access Application Block.
Thanks!
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",
arParms(1).Value = "Anne"
ds = SqlHelper.ExecuteDataset(c
The SqlHelper is from MS Data Access Application Block.
Thanks!
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.