how to send optional parameters in sqlparameter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

how to send optional parameters in sqlparameter

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-07-19 : 17:32:38

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!

dfiala
Posting Yak Master

116 Posts

Posted - 2006-07-19 : 19:59:01

arParms(0).Value = DbNull.Value

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-07-20 : 04:30:41

Excuse my lack of info in this arena....but surely you could just 'skip' all references to the param you want to be null in the calling code?
i.e.

Dim arParms() As SqlParameter = New SqlParameter(1) {}
...delete
...delete
arParms(1) = New SqlParameter("@firstname", SqlDbType.VarChar)
arParms(1).Value = "Anne"
ds = SqlHelper.ExecuteDataset(cn_str_Pubs_Dev, CommandType.StoredProcedure, "GetAuthors2", arParms)

dfiala
Posting Yak Master

116 Posts

Posted - 2006-07-20 : 10:25:52

That should work too because you're using named parameters and are not relying on ordinal position for the parameters. I do believe it causes a little more traffic bewteen the app and SQL server to query the SP's parameters, but I haven't dragged out profiler to confirm it.

Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources