Prev: cheap chanel bags
Next: MS SQL Server 2008 Administration Certification Test
From: crocboy25 on 7 Jul 2010 08:28
Hello,

I have a short procedure which compiles fine. When the code is called
from a webpage(.net) it runs the first time it is executed. However on
the second time it throws an error saying "There is already an object
named 'strPKHoldUp' in the database." StrPKHold is a variable that I
declare in the code as seen below.... I have never had a problem
before with declaring a variable and having it "save or cache" in the
database so you can use it again. Any idea how to solve this? I tried
moving the DECLARE statement around a little bit but that didnt help.
I changed the variable name to something else and it works one time
but fails after that...

Thanks in advance...

USE [MITIGATION_REPORTING]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UploadPictureFile]
(
@MIT_FK INT,
@FILE_TYPE CHAR(1),
@inFILE VARBINARY(MAX),
@CRE_USER VARCHAR(10),
@CRE_DATE DATETIME,
@MOD_USER VARCHAR(10) = null,
@MOD_DATE DATETIME = null
--@kFileName bigint output
)
AS



BEGIN

DECLARE @strPKHoldUp int

-- PUT IN THE CODE TO SEE IF THE PK AND THE TYPE ALREADY EXIST. IF SO,
UPDATE. IF NOT THEN INSERT.
select MR_MITIGATION_UPLOADS_MITIGATION_FK into strPKHoldUp
from dbo.MR_MITIGATION_UPLOADS
where MR_MITIGATION_UPLOADS_MITIGATION_FK = @MIT_FK
and MR_MITIGATION_UPLOADS_TYPE = @FILE_TYPE;


-- if no record was found, meaning that one needs to be inserted
if @strPKHoldUp = 0

begin
insert into
DBO.MR_MITIGATION_UPLOADS(MR_MITIGATION_UPLOADS_MITIGATION_FK,
MR_MITIGATION_UPLOADS_TYPE,
MR_MITIGATION_UPLOADS_FILE,
MR_MITIGATION_UPLOADS_CRE_USER,
MR_MITIGATION_UPLOADS_CRE_DATE,
MR_MITIGATION_UPLOADS_MOD_USER,
MR_MITIGATION_UPLOADS_MOD_DATE)
values (@MIT_FK,
@FILE_TYPE,
@inFILE,
@CRE_USER,
@CRE_DATE,
@MOD_USER,
@MOD_DATE)
--set @kFileName = @@IDENTITY
end


-- a record was found so we need to update the record
if @strPKHoldUp > 0

begin


update dbo.MR_MITIGATION_UPLOADS
set MR_MITIGATION_UPLOADS_FILE = @inFILE
where MR_MITIGATION_UPLOADS_MITIGATION_FK = @MIT_FK
and MR_MITIGATION_UPLOADS_TYPE = @FILE_TYPE;



end

END
From: David Martin on 7 Jul 2010 09:39
On 07/07/2010 02:28 PM, crocboy25 wrote:
> Hello,
>
> I have a short procedure which compiles fine. When the code is called
> from a webpage(.net) it runs the first time it is executed. However on
> the second time it throws an error saying "There is already an object
> named 'strPKHoldUp' in the database." StrPKHold is a variable that I
> declare in the code as seen below....

'StrPKHold' is not a variable, it is a table that exists in the database.
If you put '@' before its name, it will be considered a variable:
select MR_MITIGATION_UPLOADS_MITIGATION_FK into @strPKHoldUp

--
David Martin
From: Bob Barrows on 7 Jul 2010 10:11
crocboy25 wrote:
> Hello,
>
> I have a short procedure which compiles fine. When the code is called
> from a webpage(.net) it runs the first time it is executed. However on
> the second time it throws an error saying "There is already an object
> named 'strPKHoldUp' in the database." StrPKHold is a variable that I
> declare in the code as seen below.... I have never had a problem
> before with declaring a variable and having it "save or cache" in the
> database so you can use it again. Any idea how to solve this? I tried
> moving the DECLARE statement around a little bit but that didnt help.
> I changed the variable name to something else and it works one time
> but fails after that...
>
> DECLARE @strPKHoldUp int
>
> -- PUT IN THE CODE TO SEE IF THE PK AND THE TYPE ALREADY EXIST. IF SO,
> UPDATE. IF NOT THEN INSERT.
> select MR_MITIGATION_UPLOADS_MITIGATION_FK into strPKHoldUp
....
> if @strPKHoldUp = 0

As David says "into strPKHoldUp" does not assign a value to
"strPKHoldUp", it creates a table called "strPKHoldUp" and inserts the
rows generated by your sql statement into it. Your code seems to work
for you the first time because an int variable contains 0 when it is
declared, and you weren't testing the situation where the key already
exists, in which case, this code would have quickly failed for you.

However, he failed to correct your syntax: to assign a value to a scalar
variable, you need to use "=", not "into". Also, you need to avoid
unintended consequences if your sql statement returns more than one row.
When assigning a value to a scalar variable from a sql statement, you
need to make sure that the sql statement only returns a single row,
something like this:

SET @strPKHoldUp = (SELECT TOP 1 MR_MITIGATION_UPLOADS_MITIGATION_FK
.... )

Note: I've come to prefer this syntax when assigning a value to a single
variable, only using the "SELECT @variable=column_value FROM table"
syntax when assigning values from several columns in the same row to
several variables

--
HTH,
Bob Barrows


 | 
Pages: 1
Prev: cheap chanel bags
Next: MS SQL Server 2008 Administration Certification Test