SqlServer建立存储过程,方便.NET插入自增字段

create table tablesno
(
    tablename varchar(30) not null,--表名
    num int not null --行数
)

USE [test]        --数据库名
GO
/****** Object:  StoredProcedure [dbo].[usp_Id]    Script Date: 2017/2/1 星期三 下午 6:48:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create  proc [dbo].[usp_Id]        --新建一个存储过程名为usp_Id
 @tablename nvarchar(50),@id int output
as
declare @erro int
set @erro=0
begin transaction
　　SELEct @id=num+1 from tablesno where tablename=@tablename
　　set @erro=@erro+@@ERROR
　　update tablesno set num=num+1 where tablename=@tablename
　　set @erro=@erro+@@ERROR
　　if(@erro=0)
　　　　begin
　　　　commit transaction
　　　　end
   else
　　　　begin
　　　　rollBACk transaction
　　　　end

public class Commonservice              
    {
        public static int GetId(String tableName)           //存储过程ID
        {
            int id = 0;
            String sql = "usp_Id";
            sqlParameter par1 = new sqlParameter("@tablename",tableName);
            par1.Direction = ParameterDirection.Input;
            sqlParameter par2 = new sqlParameter("@id",sqlDbType.int);
            par2.Direction = ParameterDirection.output;
            sqlConnection con = null;
            sqlCommand cmd = null;
            try
            {
                con = sqlHelper.open();
                cmd = new sqlCommand();
                cmd.Connection = con;
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(par1);
                cmd.Parameters.Add(par2);
                cmd.ExecuteNonQuery();
                id = Convert.ToInt32(cmd.Parameters["@id"].value);

            }
            catch (sqlException eX)
            {

            }
            finally
            {
                con.Close();
            }

            return id;

        }
    }

Id = Commonservice.GetId("ClickAccessamount");