Re: calling stored procedure with optional parameter

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

  • Passing Optional Parameter to Stored Procedure in VS VB.net
    ... I'm trying to pass only the required parameters to a stored procedure using ... select @pstatusid = statusid ... insert into Employee ...
    (microsoft.public.dotnet.framework.adonet)
  • Mass Insert :: Prog Loop Form and pass to SP?
    ... multiple selections for a particular user and then pass it to the stored ... STORED PROCEDURE ... @StaffID int, ... INSERT INTO AlertSelection(StaffID, StatusID, AlertBLN) ...
    (microsoft.public.inetserver.asp.db)
  • Re: Parameterized queries
    ... named parameters are for stored procedures. ... drop the use of parameters if you want to dynamically build your SQL Insert ... But this has the disadvantage that I need a stored procedure for every ... Set param = insert_cmd.CreateParameter(_ ...
    (microsoft.public.access.adp.sqlserver)
  • Re: New row not shown in Access after INSERT
    ... queries in an Access file like a stored procedure on an SQL Server. ... the named parameters is another issue in question - Not sure ... I'd rather not use just straight SQL commands. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Parameter Order
    ... You are not using named parameters here, and therefore @StatusId and @AssetCode are interchanged. ... connAsset) ...
    (microsoft.public.dotnet.framework.adonet)