I created a store procedure , I want to lock a table until processing complete, and no one else could alter the table record between processing .. em generating sequential number i want that when user will enter the NofBin=50 result should be like next 50 comma seperated connective list. i.e 000001,000002,000003,.....000050, although this procedure is working f9, there is a concept of transaction and table locking .. how I will achieve this any favor ? ALTER PROCEDURE [dbo].[SF_GenerateBinCode] @NoofBin int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; Declare @Count int; Declare @Result varchar(max) set @Count=0; WHILE @Count < @NoofBin BEGIN set @count=@count+1; Declare @maxseq bigint Select top 1 @maxseq= CAST( LastBin as bigint) from LastBin order by LastBin desc if @maxseq is null BEGIN set @maxseq=0 END set @maxseq=@maxseq+1; set @Result= RIGHT(000000 + CONVERT(varchar(6),@maxseq), 6)+,; INSERT INTO [LastBin] ( [LastBin] ) VALUES ( @maxseq ); END IF @@Error0 BEGIN Select Error_Message() as Result; END Else BEGIN Select @Result as Result; END END
Posted on: Thu, 11 Sep 2014 12:52:25 +0000
Trending Topics
Recently Viewed Topics
© 2015