dear all,
i have made a stored procedure to generate auto id . when iexecute the pro i get folowing error:
Incorrect syntax near '0'. at the highlighted line .if i comment this line then i get this error: Could not find stored procedure 'GRN'. at the last line.I don know the real problem.my procedure is here.
ALTER PROCEDURE mp_GenerateID
@tableName as varchar(50)='GRN_PART_MASTER' AS
BEGIN
declare @Dt varchar(max);
declare @ID varchar(max);
declare @mMonth varchar(max);
select @Dt = 'Select GetDate()';
if @tableName = 'GRN_PART_MASTER'
begin
set @ID = 'Select GRNID from GRN_PART_MASTER ORDER BY GRNID DESC'
IF @ID <> ''
if Substring(@ID ,6, 2) = substring(convert(varchar,datepart(year,GETDATE())),3,2)
begin
set @mMonth = 'Substring(ID ,8, 2)'
if len(datepart(month, GETDATE())) <> 2
if @mMonth = '0' + 'select datepart(month, GETDATE())'
set @ID = 'Substring(@ID ,1,11)' + 'cast( Substring(@ID ,11,3) as int)' + '1';
else if @mMonth = 'select datepart(month, GETDATE())'
set @ID = 'Substring(@ID ,1,11)' + 'cast( Substring(@ID ,11,3)as int) ' + '1';
else
goto GenID;
end
else
GenID:
if len(datepart(month, GETDATE())) <> 2
else
set @ID = 'GRN-1' + 'select substring(convert(varchar,datepart(year,GETDATE())),3,2)' + 'select substring(convert(varchar,datepart(month,GETDATE())),1,2)' + '/001';
end
EXEC (@ID)
END |
