p2p.wrox.com: Archives - asp_web

asp_web_howto thread: Stored Proc Question

Message #1 by "Ryan vd Merwe" <ryanvdm@i...> on Thu, 20 Feb 2003 13:26:29 +0200


Hi

I'm not to familiar with stored procs

I have a stored proc that inserts some values...

Alter PROCEDURE InsertBranch
        (@branchName    [nvarchar](50),
         @branchTel     [nvarchar](50))

AS INSERT INTO [hifiCorp].[dbo].[HIFIBranch]
         ( [branchName],
         [branchTel])

VALUES
        ( @branchName,
         @branchTel)


I want to check first to see if the record I am adding already exits. Can
this be done within the stored Proc and if so How do I change the proc
above.

Thanks

Ryan

Message #2 by Mark Eckeard <meckeard2000@y...> on Thu, 20 Feb 2003 05:56:20 -0800 (PST)

Try this:

declare @intExistingCount  int

select @intExistingCount = count(*)
from HIFIBranch
where
   upper(branchName) = upper(@branchName)
   and upper(branchTel) = upper(@branchTel)

-- Check to make sure the count = 0 to insert
if intExistingCount = 0 
begin
   do your insert code here
end

Mark.
--- Ryan vd Merwe <ryanvdm@i...> wrote:
> 
> 
> Hi
> 
> I'm not to familiar with stored procs
> 
> I have a stored proc that inserts some values...
> 
> Alter PROCEDURE InsertBranch
>         (@branchName    [nvarchar](50),
>          @branchTel     [nvarchar](50))
> 
> AS INSERT INTO [hifiCorp].[dbo].[HIFIBranch]
>          ( [branchName],
>          [branchTel])
> 
> VALUES
>         ( @branchName,
>          @branchTel)
> 
> 
> I want to check first to see if the record I am
> adding already exits. Can
> this be done within the stored Proc and if so How do
> I change the proc
> above.
> 
> Thanks
> 
> Ryan
> 
> 
> 
> 
> 
> 
> 


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

Message #3 by "Ken Schaefer" <ken@a...> on Fri, 21 Feb 2003 16:20:57 +1100

CREATE PROC usp_InsertBranch

    @branchName    nvarchar(50),
    @branchTel        nvarchar(50)

AS

    IF EXISTS
    (
        SELECT
            NULL
        FROM
           dbo.HIFIBranch AS a
        WHERE
            a.branchName = @branchName
        AND
            a.branchTel = @branchTel
    )
    BEGIN
        RETURN -1
    END
    ELSE
    BEGIN

        INSERT INTO
            ...
            ...
        --- your existing stuff here

    END

GO

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ryan vd Merwe" <ryanvdm@i...>
Subject: [asp_web_howto] Stored Proc Question


: I'm not to familiar with stored procs
:
: I have a stored proc that inserts some values...
:
: Alter PROCEDURE InsertBranch
:         (@branchName    [nvarchar](50),
:          @branchTel     [nvarchar](50))
:
: AS INSERT INTO [hifiCorp].[dbo].[HIFIBranch]
:          ( [branchName],
:          [branchTel])
:
: VALUES
:         ( @branchName,
:          @branchTel)
:
:
: I want to check first to see if the record I am adding already exits. Can
: this be done within the stored Proc and if so How do I change the proc
: above.
:
: Thanks
:
: Ryan
:
:
:
:
:
:
:

Message #4 by "Ryan vd Merwe" <ryanvdm@i...> on Fri, 21 Feb 2003 11:10:37 +0200

Great

One more question is it better to write an application almost totally using
stored Procs?



-----Original Message-----
From: Mark Eckeard [mailto:meckeard2000@y...]
Sent: 20 February 2003 03:56
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Stored Proc Question


Try this:

declare @intExistingCount  int

select @intExistingCount = count(*)
from HIFIBranch
where
   upper(branchName) = upper(@branchName)
   and upper(branchTel) = upper(@branchTel)

-- Check to make sure the count = 0 to insert
if intExistingCount = 0
begin
   do your insert code here
end

Mark.
--- Ryan vd Merwe <ryanvdm@i...> wrote:
>
>
> Hi
>
> I'm not to familiar with stored procs
>
> I have a stored proc that inserts some values...
>
> Alter PROCEDURE InsertBranch
>         (@branchName    [nvarchar](50),
>          @branchTel     [nvarchar](50))
>
> AS INSERT INTO [hifiCorp].[dbo].[HIFIBranch]
>          ( [branchName],
>          [branchTel])
>
> VALUES
>         ( @branchName,
>          @branchTel)
>
>
> I want to check first to see if the record I am
> adding already exits. Can
> this be done within the stored Proc and if so How do
> I change the proc
> above.
>
> Thanks
>
> Ryan
>
>
>
>
>
>
>


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/

Message #5 by "Ryan vd Merwe" <ryanvdm@i...> on Fri, 21 Feb 2003 13:16:59 +0200

thanks!

-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: 21 February 2003 07:21
To: ASP Web HowTo
Subject: [asp_web_howto] Re: Stored Proc Question


CREATE PROC usp_InsertBranch

    @branchName    nvarchar(50),
    @branchTel        nvarchar(50)

AS

    IF EXISTS
    (
        SELECT
            NULL
        FROM
           dbo.HIFIBranch AS a
        WHERE
            a.branchName = @branchName
        AND
            a.branchTel = @branchTel
    )
    BEGIN
        RETURN -1
    END
    ELSE
    BEGIN

        INSERT INTO
            ...
            ...
        --- your existing stuff here

    END

GO

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Ryan vd Merwe" <ryanvdm@i...>
Subject: [asp_web_howto] Stored Proc Question


: I'm not to familiar with stored procs
:
: I have a stored proc that inserts some values...
:
: Alter PROCEDURE InsertBranch
:         (@branchName    [nvarchar](50),
:          @branchTel     [nvarchar](50))
:
: AS INSERT INTO [hifiCorp].[dbo].[HIFIBranch]
:          ( [branchName],
:          [branchTel])
:
: VALUES
:         ( @branchName,
:          @branchTel)
:
:
: I want to check first to see if the record I am adding already exits. Can
: this be done within the stored Proc and if so How do I change the proc
: above.
:
: Thanks
:
: Ryan
:
:
:
:
:
:
:

Return to Index

P2P Archives