SteveC

drop proc api_post_reply_post;
go
create proc api_post_reply_post
@t_id int,
@u_id int,
@reply_p_id bigint,
@title nvarchar(256),
@body nvarchar(4000),
@test_p_id bigint output,
@test_msg varchar(2048) output
as
set nocount on;
set xact_abort on;

begin transaction
begin try
declare
@inserted_reply table(act nvarchar(20),
p_id bigint);

with
branch_cte(p_id, orig_p_id, reply_p_id, tree_path, reply_depth_count, created_dt, calc)
as (
select
p_id,
orig_p_id,
cast(null as bigint),

drop proc api_post_post;
go
create proc api_post_post
@t_id int,
@u_id int,
@title nvarchar(256),
@body nvarchar(4000),
@test_p_id bigint output,
@test_msg varchar(2048) output
as
set nocount on;
begin try
insert posts(t_id, u_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values(@t_id, @u_id, 2, 2, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_p_id=scope_identity();
select @test_msg='Ok';
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();;

if object_id('dbo.posts') is not null
drop table if exists thr.posts;
create table posts(
p_id bigint identity(2,2) primary key not null,
t_id int not null references threads(t_id),
u_id int not null references users(id),
orig_p_id bigint null references posts(p_id),
reply_p_id bigint null references posts(p_id),
reply_depth_count int not null default 0,
tree_path int not null,
tree_path_bit bit not null default 0,
title nvarchar(256) null,
body nvarchar(4000) not null,