Re: calling stored procedure with optional parameter

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdpronamedparameters.asp

Enable the NamedParameters property and you only have to append Parameter
objects for the parameters you want to send to the SP.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"wk6pack" <wkwan@xxxxxxxxxx> wrote in message
news:O8m2n4VkFHA.2720@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Bill,
>
> here is my sp. How do I call it using named parameters? I thought I was
> doing it when I created the parameter?
>
> CREATE PROCEDURE dbo.Insert_Employee
> (
> @pemployeeno nvarchar(10),
> @S_I_N nvarchar(10),
> @pfirstname nvarchar(50),
> @plastname nvarchar(50),
> @pmiddlename nvarchar(50)=" ",
> @pprimaryemail nvarchar(50)=" ",
> @psecondaryemail nvarchar(50) = " ",
> @status_code nvarchar(10) = " "
> )
> AS
> declare @pstatusid numeric,
> @identity int
>
> select @pstatusid = statusid
> from status
> where statuscode = @status_code;
>
> insert into Employee
> (
> Employeeno,
> S_I_N,
> firstname,
> lastname,
> middlename,
> primaryemail,
> secondaryemail,
> statusid
> )
> values
> (
> @pemployeeno,
> @S_I_N,
> @pfirstname,
> @plastname,
> @pmiddlename,
> @pprimaryemail,
> @psecondaryemail,
> @status_code
> )
> select @identity = @@identity
> RETURN
> GO
>
> Thanks,
> Will
>
> "William (Bill) Vaughn" <billvaRemoveThis@xxxxxxxxxx> wrote in message
> news:eFqS4YVkFHA.2472@xxxxxxxxxxxxxxxxxxxxxxx
>> How is the SP defined? Are there default values supplied for the optional
>> parameters?
>>
>> I expect you'll need to request "Named Parameters" so you can provide a
>> sparse Parameter list.
>>
>> --
>> ____________________________________
>> William (Bill) Vaughn
>> Author, Mentor, Consultant
>> Microsoft MVP
>> www.betav.com/blog/billva
>> www.betav.com
>> Please reply only to the newsgroup so that others can benefit.
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> __________________________________
>>
>> "wk6pack" <wkwan@xxxxxxxxxx> wrote in message
>> news:%235ueWCVkFHA.3692@xxxxxxxxxxxxxxxxxxxxxxx
>> > Hi,
>> >
>> > Is it possible to call a stored procedure from vb.net when some of the
>> > parameters are not required in the stored procedure?
>> > Say i have a stored procedure with 3 parameters. I want to call the
>> > stored
>> > procedure with only two of the parameters.
>> > I seem to get an error saying I need the 3rd parameter. When I fill in
>> > the
>> > 3 parameter the sql statement call from vb.net works fine.
>> >
>> > code:
>> > cmd.CommandText = "InsertVSIS"
>> > cmd.CommandType = adCmdStoredProc
>> > cmd.Parameters.Append cmd.CreateParameter("@studentkey", adInteger,
>> > adParamInput,,lstudentkey)
>> >
>> >
>> > thanks,
>> > Will
>> >
>> >
>>
>>
>
>


.

Relevant Pages