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