July 7, 2011 at 1:41 pm
I'm trying to create a batch file that I can hand off and someone else can run to update our schema and such. Inside the batch file I'd like to create a stored procedure, but I keep getting the errors:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 137, Level 15, State 2, Line 19
Must declare the scalar variable "@LName".
Msg 178, Level 15, State 1, Line 20
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 22
A RETURN statement with a return value cannot be used in this context.
If I run the CREATE PROCEDURE on it's own it works just fine. What am I doing wrong?
This is my batch file:
USE testdb
GO
-- If a change statement fails we want to rollback the entire transaction
SET XACT_ABORT ON
GO
DECLARE @ErrorData TABLE (ErrorNumber INT, ErrorMessage NVARCHAR(MAX))
BEGIN TRANSACTION ChangeSchema
BEGIN TRY
-- All schema changes should be placed below this line
-- New column for only the last name that is unique
ALTER TABLE myUsers ADD LName VARCHAR(32) NULL CONSTRAINT UNQ__myUsers__LName UNIQUE
-- Index changes should be placed below this line
-- All stored procedure changes should be placed below this line
-- Allow to check that a proposed last name is unique before inserting it
CREATE PROCEDURE IsLNameUnique
@LName varchar(32)
AS
IF EXISTS (SELECT LName FROM Characters WHERE LName = @LName)
RETURN (1)
ELSE
RETURN (0)
END TRY
BEGIN CATCH
INSERT INTO @ErrorData SELECT ERROR_NUMBER(), ERROR_MESSAGE()
END CATCH
IF EXISTS (SELECT * FROM @ErrorData)
BEGIN
-- Turn this setting back off when done
PRINT 'An error has occurred! Rolling back all changes.';
SET XACT_ABORT OFF;
ROLLBACK TRANSACTION ChangeSchema
END
ELSE
BEGIN
COMMIT TRANSACTION ChangeSchema
-- Turn this setting back off when done
SET XACT_ABORT OFF;
END
July 7, 2011 at 1:46 pm
I figured it out. I need to wrap the CREATE PROCEDURE in an exec():
exec('CREATE PROCEDURE IsLNameUnique
@LName varchar(32)
AS
IF EXISTS (SELECT LName FROM Characters WHERE LName = @LName)
RETURN (1)
ELSE
RETURN (0)')
July 7, 2011 at 3:09 pm
No reason to use error-handling for a DDL script. Just check if the object exists and create it if not, like so:
IF NOT EXISTS (SELECT 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'myUsers' and COLUMN_NAME = 'LName')
ALTER TABLE myUsers ADD LName VARCHAR(32) NULL CONSTRAINT UNQ__myUsers__LName UNIQUE
GO
IF OBJECT_ID(N'IsLNameUnique') IS NOT NULL
DROP PROCEDURE IsLNameUnique
GO
CREATE PROCEDURE IsLNameUnique
@LName varchar(32)
AS
IF EXISTS (SELECT LName FROM Characters WHERE LName = @LName)
RETURN (1)
ELSE
RETURN (0)
GO
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 7, 2011 at 3:20 pm
I'll give that a shot, thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy