syntax error in stored procedure

[dinivan]

I'm new to sql and have the following stored procedure but when i check the syntax i keep getting the following error

Incorrect syntax near the keyword "begin".

Its probably something simple but i just can't see it.

create procedure add_policy
@policyRef int (4),
@fileRef nvarchar (50),
@linkedDoc nvarchar (50),
@policySource nvarchar (50),
@leadPerson nvarchar (50),
@writtenDate nvarchar (50),
@reviewedDate nvarchar (50)

begin
Insert into tbl_policies_sub_donna (PolicyID)
select PolicyID 'int' from inserted

INSERT INTO tbl_policy_main_donna
(Policy_Ref, File_Reference, linked_document)
VALUES( @policyRef, @fileRef, @linkedDoc )

-- I guess your policy_id is a identity field
declare @cur_id as int
select @cur_id = IDENT_CURRENT('tbl_policy_main_donna')

INSERT INTO tbl_policies_sub_donna(policy_id, Source,
Lead_Person, Date_Written, Date_Revised)
VALUES(@cur_id, @policySource, @leadPerson, @writtendate,
@revieweddate)

end

 

[alexanderthegreat]

You need a comma next at the end of (50),

 

[Jamfool]

CREATE PROCEDURE <procedure_name, sysname, proc_test>
<@param1, sysname, @p1> <datatype_for_param1, , int> = <default_value_for_param1, , 0>,
<@param2, sysname, @p2> <datatype_for_param2, , int> = <default_value_for_param2, , 0>

AS

...rest of code

 

[SQLDenis]

you forgot as

create procedure add_policy
@policyRef int (4),
@fileRef nvarchar (50),
@linkedDoc nvarchar (50),
@policySource nvarchar (50),
@leadPerson nvarchar (50),
@writtenDate nvarchar (50),
@reviewedDate nvarchar (50)
as
begin


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLBill]

Dinivan,

You don't need any more commas, but Jamfool and SQLDenis are correct...AS is required when creating stored procedures.

-SQLBill

Posting advice: FAQ481-4875

 

[dinivan]

I did as advised but now because i have defined policyRef as an int, i am now getting the error message:

Cannot specify a column width on data type int. Parameter '@policyRef' has an invalid datatype.

Is there anyway around this ??

create procedure add_policy
@policyRef int (4),
@fileRef nvarchar (50),
@linkedDoc nvarchar (50),
@policySource nvarchar (50),
@leadPerson nvarchar (50),
@writtenDate nvarchar (50),
@reviewedDate nvarchar (50)

AS

begin
Insert into tbl_policies_sub_donna (PolicyID)
select PolicyID 'int' from inserted

INSERT INTO tbl_policy_main_donna
(Policy_Ref, File_Reference, linked_document)
VALUES( @policyRef, @fileRef, @linkedDoc )

-- I guess your policy_id is a identity field
declare @cur_id as int
select @cur_id = IDENT_CURRENT('tbl_policy_main_donna')

INSERT INTO tbl_policies_sub_donna(policy_id, Source,
Lead_Person, Date_Written, Date_Revised)
VALUES(@cur_id, @policySource, @leadPerson, @writtendate,
@revieweddate)

end

Any help would really be appreciated

cheers dinivan

 

[SQLDenis]

@policyRef int (4), should be @policyRef int,
you don't specify a size for int

you could use smallint,int,bigint or tinyint if you need smaller or bigger ranges

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/