How to read parameter from stored procedure ?
-
Hi, I am using ADO tecnology to execute stored procedure. Does anyone know how to read out a parameter from sp into VC++ variable I need to get @file_id (which is set at the bottom of sp) thank you, bartek My stored procedure: CREATE PROCEDURE [sp_StandardFileNew] @receive_date varchar(20), @pj_id int, @pj_type varchar(20), @file_group varchar(200), @name varchar(255), @ext varchar(10), @total_words int, @dtp_pages int, @notes varchar(500) AS DECLARE @file_id int, @receive_date2 smalldatetime, @project_id int, @job_id int, @group_id int -- Check if obligatory text parameters are null if @name is null OR @ext is null OR @pj_id is null OR @pj_type is null OR @receive_date is null begin return 1 end -- Set @project_id or @job_id depending on the @pj_type [ "job" | "project" ] if @pj_type="job" begin set @project_id=null set @job_id=@pj_id end else if @pj_type="project" begin set @project_id=@pj_id set @job_id=null end else begin return 2 end -- Check group_txt if it exists retrieve its id or create new group if it does not exist if @file_group is not null begin exec sp_NewFileGroup @file_group, null, @project_id, @job_id, @group_id OUTPUT if @group_id is null begin return 3 end end else begin set @group_id = null end -- Convert date-string format to datetime / smalldatetime exec sp_DateConvert @receive_date, @receive_date2 OUTPUT -- insert new row to the Files table and convert text arguments to integer values insert into files(name, ext, receive_date, group_id, job_id, project_id, total_words, dtp_pages, file_type_id, deleted, works_count, notes) values(@name, @ext, @receive_date2, @group_id, @job_id, @project_id, @total_words, @dtp_pages, 0, 0, 0, @notes) set @file_id = @@identity if @file_id is null begin return 4 end return 0 GO
-
Hi, I am using ADO tecnology to execute stored procedure. Does anyone know how to read out a parameter from sp into VC++ variable I need to get @file_id (which is set at the bottom of sp) thank you, bartek My stored procedure: CREATE PROCEDURE [sp_StandardFileNew] @receive_date varchar(20), @pj_id int, @pj_type varchar(20), @file_group varchar(200), @name varchar(255), @ext varchar(10), @total_words int, @dtp_pages int, @notes varchar(500) AS DECLARE @file_id int, @receive_date2 smalldatetime, @project_id int, @job_id int, @group_id int -- Check if obligatory text parameters are null if @name is null OR @ext is null OR @pj_id is null OR @pj_type is null OR @receive_date is null begin return 1 end -- Set @project_id or @job_id depending on the @pj_type [ "job" | "project" ] if @pj_type="job" begin set @project_id=null set @job_id=@pj_id end else if @pj_type="project" begin set @project_id=@pj_id set @job_id=null end else begin return 2 end -- Check group_txt if it exists retrieve its id or create new group if it does not exist if @file_group is not null begin exec sp_NewFileGroup @file_group, null, @project_id, @job_id, @group_id OUTPUT if @group_id is null begin return 3 end end else begin set @group_id = null end -- Convert date-string format to datetime / smalldatetime exec sp_DateConvert @receive_date, @receive_date2 OUTPUT -- insert new row to the Files table and convert text arguments to integer values insert into files(name, ext, receive_date, group_id, job_id, project_id, total_words, dtp_pages, file_type_id, deleted, works_count, notes) values(@name, @ext, @receive_date2, @group_id, @job_id, @project_id, @total_words, @dtp_pages, 0, 0, 0, @notes) set @file_id = @@identity if @file_id is null begin return 4 end return 0 GO
You need to change the parameter declaration in your stored procedure
..
DECLARE @file_id int OUTPUT,
..This will then return the value for you when you execute the code via ADO. One thing to remember is that you will need to close your recordset down before you can read the value. This is only if you are not also returning a recordset :cool: