error in SP: expecting External

Posted by Rickeybglr under Sql Server on 5/14/2012 | Points: 10 | Views : 6035 | Status : [Member] | Replies : 2
I am getting error :
incoorect syntax near Begin expecting External. i checked many times but not able to resolve it.
Below is my SP


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'USP_CreateUser')
DROP PROCEDURE USP_SSOCreateUser

GO

IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='User'Info)
BEGIN

CREATE PROCEDURE [dbo].USP_CreateUser
(
@UserName VARCHAR(50),
@UserPswd VARCHAR(15),
@EmailID VARCHAR (30),
@ContactNo NUMERIC(18,0),
@Location VARCHAR(15),
@ApproverPerson INT,
@ProjectName INT
)
AS
BEGIN TRY
BEGIN TRANSACTION

INSERT INTO UserInfo
(UserName,UserPswd,EmailID,ContactNo,Location,ApproverPerson,ProjectName)
VALUES(
@UserName,@UserPswd,@EmailID,@ContactNo,@Location,@ApproverPerson,@ProjectName)


COMMIT TRANSACTION

SELECT 'Insert Successfully...'
END TRY


BEGIN CATCH
ROLLBACK TRANSACTION
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
--select 3

SET NOCOUNT ON;
END CATCH
END

BEGIN
SELECT 'TABLE doesnt exists'
END




Responses

Posted by: Hariinakoti on: 5/14/2012 [Member] Starter | Points: 25

Up
0
Down
hi rickey bglr,
i think collation is changed.so recreate that table.Some times itz happen.

Thanks&Regards
Hari

Thanks & Regards
Hari

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 5/14/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Please move your procedure body into BEGIN....END block. Instead of starting with BEGIN TRY directly

i.e:
BEGIN

BEGIN TRY
BEGIN TRAN
--.......
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
END


Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Rickeybglr, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response