sql - How to set a column value if unavailable -
i have table follows . table name : testnumbers
id(int) number(int) name(nvarchar(50)) 1 1 test 2 1 test 3 2 test2 when insert number column need increment if unavailable , if available max id value + 1 , ensure if number has been passed argument save is.(this ensure constraint rows name same have number column same too).
i have written sp there few syntax issues.
create procedure test_storedproc @name nvarchar(50), @number int = 0, @id int begin set nocount on begin try declare @incrementid smallint; set @incrementid=1; declare @incrementnumber int; set @incrementnumber = 1; begin tran begin if @number = 0 begin set @number = select isnull(max(number),0)+@incrementnumber testnumbers end insert testnumbers ([id] ,[name] ,[number]) select isnull(max([id]),0)+@incrementid [id] ,@name ,@number testnumbers --return id of column inserted select max([id]) testnumbers end commit tran end try begin catch --to end catch end go how improve , correct sp?
create procedure sp_test @name nvarchar(50), @number int = 0, @id int begin set nocount on begin try declare @incrementid smallint; set @incrementid=1; declare @incrementnumber int; set @incrementnumber = 1; begin tran begin if @number = 0 *begin **set @number =(select isnull(max(number),0)+@incrementnumber testnumbers)*** end insert testnumbers ([id] ,[name] ,[number]) select isnull(max([id]),0)+@incrementid [id] ,@name ,@number testnumbers --return id of column inserted select max([id]) testnumbers end commit tran end try **begin catch rollback tran end catch** end there 2 syntax error in stored procedure
you should have add round bracket when assign value variable through sql select query.
i think u forgot catch block after try block necessary else catch exception , necessary transaction after error :), additionally procedure have logic, keep up. syntax google there....
Comments
Post a Comment