This is only my third stored procedure/UDF so this is newbie territory.
Essentially my SP conducts an INSERT, I want to take the id number from the insert and include it in two subsequent inserts.
While the first insert works okay the value that is supposed to be the field number appear to be something else and the query analyser returns the error:
"Server: Msg 8114, Level 16, State 5, Procedure hsdms_create_user, Line 38
Error converting data type varchar to bigint."

So can anyone please point out my blindingly obvious miscode?
Thanks in advance.
code is as follows:
================================================== ======
CREATE PROCEDURE hsdms_admin.hsdms_create_user
@username_in varchar(10),
@password_in varchar(40),
@email_in varchar(100),
@firstname_in varchar(100) = NULL,
@familyname_in varchar(100) = NULL,
@title_in varchar(25) = NULL,
@suffix_in varchar(100) = NULL,
@output_result bigint = 0 OUTPUT
AS

DECLARE @party_table_name varchar(50)
DECLARE @party_table_id varchar(50)
DECLARE @party_id_value bigint
DECLARE @person_table_name varchar(50)
DECLARE @person_table_id varchar(50)
DECLARE @user_table_name varchar(50)
DECLARE @user_table_id varchar(50)
DECLARE @sql nvarchar(255)

SET @party_table_name = dbo.hsdms_get_table_name('party')
SET @party_table_id = dbo.hsdms_get_id_column('party')
SET @person_table_name = dbo.hsdms_get_table_name('person')
SET @person_table_id = dbo.hsdms_get_id_column('person')
SET @user_table_name = dbo.hsdms_get_table_name('user')
SET @user_table_id = dbo.hsdms_get_id_column('user')

SET NOCOUNT OFF
SELECT @sql = 'IF NOT EXISTS(SELECT ' + @user_table_id + ' FROM ' + @user_table_name + ' where username = ' + @username_in + ')'
BEGIN
SET QUOTED_IDENTIFIER ON
SET @sql = "INSERT INTO " + @party_table_name + " (email) VALUES('" + @email_in + "')"
EXEC sp_executesql @sql
--SELECT SCOPE_IDENTITY() as bigint
-- SELECT @output_result=IDENT_CURRENT(' + @party_table_name +')
SELECT @output_result=IDENT_CURRENT('party')
-- NEXT LINE IS WHERE ERROR OCCURS
SET @sql="INSERT INTO " + @person_table_name + " (" + @person_table_id + ",first_names,last_names,title,suffix) VALUES('" + @output_result + "', '" + @firstname_in + "', '" + @familyname_in + "', '" + @title_in + "', '" + @suffix_in + "')"
EXEC sp_executesql @sql
SET @sql = "INSERT INTO " + @user_table_name + " (" + @user_table_id + ",username,password) VALUES('" + @output_result + "', '" + @username_in + "', '" + @password_in + "')"
EXECUTE (@sql)
EXEC sp_executesql @sql
SET QUOTED_IDENTIFIER OFF
END
--ELSE
--BEGIN
--SET @output_result = -1
--END
SET NOCOUNT ON
-- exec hsdms_admin.hsdms_create_user @username_in='admin',@password_in='wl4843',@firstn ame_in='leigh',@familyname_in='silvester',@email_i n='[email protected]',@output_result = 0


================================================== ======

Before you try to execute the Insert...print it out. check it so that it is a valid Insert Into statement

Jim

I can get the first @sql line to PRINT out, but if I place a "PRINT @sql" directly after the second "SET @SQL ...." The SP fails before this PRINT can execute. I have tried using "SET @output_result=56" to set the value of @output_result directly prior to the second SET of @sql, but this appears not the variable generating the conversion error, although if I change
VALUES(" + @output_result + ", '" + @firstname_in + "', '" + @familyname_in + "', '" + @title_in + "', '" + @suffix_in + "')
to
VALUES(56, '" + @firstname_in + "', '" + @familyname_in + "', '" + @title_in + "', '" + @suffix_in + "')
Then it does not error, although a PRINT after the second SET @sql does not print anything out.

Any ideas gratefuly received.

Got it cracked!
Obviously @output_result is bigint, and @sql is varchar, so to create a string containing the variable @output_result I have to CAST it as a varchar.
Only taken me a week to finally figure this out.