How To reverse DECLARE @VendorID int, @CreditRati

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

General SQL Server Forums

New to SQL Server Programming

How To reverse DECLARE @VendorID int, @CreditRati

Author

Topic

dbserver2000
Starting Member

47 Posts

Posted - 2009-04-30 : 15:04:56

All I need is to reverse

DECLARE @VendorID int, @CreditRating int

I ran that script by itself, and I was supposed to run within a much bigger script.

Hope I am clear

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-30 : 15:08:34

Not the least bit clear. Did you do something like
set vendorid = @creditRating
set creditrating = @vendorid

Jim

dbserver2000
Starting Member

47 Posts

Posted - 2009-04-30 : 15:30:09

Exercise 4.8: Catching Errors

DECLARE @VendorID int, @CreditRating int
SET @vendorID=1
SET @CreditRating=50
USE Adventureworks
UPDATE Purchasing.Vendor
SET CreitRating = @CreditRating
Where VendorID = @VendorID

DECLARE @VendorID int, @CreditRating int
SET @VendorID = 1
SET @CreditRating=50
BEGIN TRY
BEGIN TRANSACTION
USE AdventureWorks
UPDATE Purchasing.Vendor
SET CreditRating = @Creditrating
Where VendorID=@VendorID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
IF @CreditRating > 5 OR @Creditrating < 1
SELECT 'Credit Rating must be between 1 and 5.'
END CATCH

SO what I did I ran the DECLARE @VendorID int, @CreditRating int
by itself. It was completed succefully

but now when I try to run the whole script
I get this message

Msg 134, Level 15, State 1, Line 9
The variable name '@VendorID' has already been declared. Variable names must be unique within a query batch or stored procedure.

So how can I reverse the DELARE if there is a way. !

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 15:31:44

You don't need to declare it twice in the same script. You may need to reinitialize the variables etc (like SET @Variable = [value]).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 15:36:40

I'm still not sure what you are trying to do but try this:

quote:
Originally posted by dbserver2000

Exercise 4.8: Catching Errors

DECLARE @VendorID int, @CreditRating int
SET @vendorID=1
SET @CreditRating=50
USE Adventureworks
UPDATE Purchasing.Vendor
SET CreitRating = @CreditRating
Where VendorID = @VendorID

GO

DECLARE @VendorID int, @CreditRating int
SET @VendorID = 1
SET @CreditRating=50
BEGIN TRY
BEGIN TRANSACTION
USE AdventureWorks
UPDATE Purchasing.Vendor
SET CreditRating = @Creditrating
Where VendorID=@VendorID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
IF @CreditRating > 5 OR @Creditrating < 1
SELECT 'Credit Rating must be between 1 and 5.'
END CATCH

SO what I did I ran the DECLARE @VendorID int, @CreditRating int
by itself. It was completed succefully

but now when I try to run the whole script
I get this message

Msg 134, Level 15, State 1, Line 9
The variable name '@VendorID' has already been declared. Variable names must be unique within a query batch or stored procedure.

So how can I reverse the DELARE if there is a way. !

dbserver2000
Starting Member

47 Posts

Posted - 2009-04-30 : 15:37:20

What does the command DECLARE do in simple English?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 15:38:05

Or even this should work for you"

quote:
Originally posted by dbserver2000

Exercise 4.8: Catching Errors

DECLARE @VendorID int, @CreditRating int
SET @vendorID=1
SET @CreditRating=50
USE Adventureworks
UPDATE Purchasing.Vendor
SET CreitRating = @CreditRating
Where VendorID = @VendorID

--DECLARE @VendorID int, @CreditRating int
--SET @VendorID = 1
--SET @CreditRating=50
BEGIN TRY
BEGIN TRANSACTION
USE AdventureWorks
UPDATE Purchasing.Vendor
SET CreditRating = @Creditrating
Where VendorID=@VendorID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
IF @CreditRating > 5 OR @Creditrating < 1
SELECT 'Credit Rating must be between 1 and 5.'
END CATCH

SO what I did I ran the DECLARE @VendorID int, @CreditRating int
by itself. It was completed succefully

but now when I try to run the whole script
I get this message

Msg 134, Level 15, State 1, Line 9
The variable name '@VendorID' has already been declared. Variable names must be unique within a query batch or stored procedure.

So how can I reverse the DELARE if there is a way. !

dbserver2000
Starting Member

47 Posts

Posted - 2009-04-30 : 15:46:40

Thanks it worked

But

What does the command DECLARE do in simple English?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-30 : 15:52:43

It declares an database object (a table, a variable etc).

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 16:11:14

DECLARE defines a local variable for the batch in which is was declared.

Are you familar with any other programming languages? It would be similar to using a DIM statement in visual basic, if you have done any VB programming.

dbserver2000
Starting Member

47 Posts

Posted - 2009-04-30 : 18:27:44

Much Clear now

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources