Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Syntax errors in a stored procedure


Hi,

I got errors about "@liError " and "ErrorHandler" not declared when
running a stored procedure below. For Heaven's sake, I declared both
loud and clear. The errors are where the comments are. Anyone can tell
me what's wrong?

-- Drop the old table
IF OBJECT_ID('dbo.#TmpSystemParameter') IS NOT NULL
BEGIN
    DROP TABLE dbo.#TmpSystemParameter
    IF OBJECT_ID('dbo.#TmpSystemParameter') IS NOT NULL
        PRINT '<<< FAILED DROPPING TABLE dbo.#TmpSystemParameter >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.#TmpSystemParameter >>>'
END

GO

DECLARE @liError int

SET @liError = @@error
IF @liError <> 0
BEGIN
        GOTO ErrorHandler
END

-- Store data in the current "SystemParameter" table to a temp table
SELECT * INTO #TmpSystemParameter
FROM dbo.SystemParameter     --Error: A GOTO statement references the
label 'ErrorHandler' but the label has not been declared.

GO

SET @liError = @@error
IF @liError <> 0
BEGIN
        GOTO ErrorHandler    --Error: Must declare the variable '@liError'.
END

-- Drop the old table
IF OBJECT_ID('dbo.SystemParameter') IS NOT NULL
BEGIN
    DROP TABLE dbo.SystemParameter
    IF OBJECT_ID('dbo.SystemParameter') IS NOT NULL
        PRINT '<<< FAILED DROPPING TABLE dbo.SystemParameter >>>'
    ELSE
        PRINT '<<< DROPPED TABLE dbo.SystemParameter >>>'
END

GO

ErrorHandler:
Return @liError

IF OBJECT_ID('dbo.SystemParameter') IS NOT NULL
    PRINT '<<< CREATED TABLE dbo.SystemParameter >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.SystemParameter >>>'
GO

GRANT EXECUTE ON dbo.SystemParameter TO CRPTroubleshooters,
GMOGMOUsers
GO

GO started a new batch. The context of a variable does not span batches.

RLF

"Curious" <fir5tsi@yahoo.com> wrote in message

news:1181057061.390627.27250@n4g2000hsb.googlegroups.com...

GO separates a batch.  You cannot persist @variables across GO statements.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"Curious" <fir5tsi@yahoo.com> wrote in message

news:1181057061.390627.27250@n4g2000hsb.googlegroups.com...

You can't use GOTO and GO's in the same batch.
A GO ends the batch.

Read up on GO in BOL

"Curious" <fir5tsi@yahoo.com> wrote in message

news:1181057061.390627.27250@n4g2000hsb.googlegroups.com...

Thanks! I removed all of the "GO"s in the middle while only keeping
the very last one at the end, and those two types of errors go away.

Now I've got another error towards the end of the SQL statements:

IF OBJECT_ID('dbo.SystemParameter') IS NOT NULL    --Error: A RETURN
statement with a return value cannot be used in this context.
    PRINT '<<< CREATED TABLE dbo.SystemParameter >>>'
ELSE
    PRINT '<<< FAILED CREATING TABLE dbo.SystemParameter >>>'

GRANT EXECUTE ON dbo.SystemParameter TO CRPTroubleshooters,
GMOGMOUsers
GO

Could you tell me what's wrong?

-----------------------------------------------Reply-----------------------------------------------

You have this statement:

Return @liError

You can only use RETURN within a function or procedure.

Like GO, you can read up on RETURN in Books Online.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

"Curious" <fir5tsi@yahoo.com> wrote in message

news:1181057636.624046.105140@k79g2000hse.googlegroups.com...

But I need to handle the errors so I have ErrorHandler. I'll need to
return the error code if there's an error. How can I do that without
using "RETURN"?

-----------------------------------------------Reply-----------------------------------------------

> But I need to handle the errors so I have ErrorHandler. I'll need to
> return the error code if there's an error. How can I do that without
> using "RETURN"?

If you're just running the code in Query Analyzer, what are you "returning"
to?  Do you mean you need to SELECT or PRINT the value?

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc