Return value from Stored Procedure is always null in delphi

Question

I've got a small issue with delphi/sql server. I have a SP

GO
/****** Object:  StoredProcedure [dbo].[sp_adaugaUser]    Script Date: 6/30/2014 12:33:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_adaugaUser]
    -- Add the parameters for the stored procedure here
    @User varchar(50), 
    @Password varchar(32)--,
   -- @errMesaj varchar(50)
AS
BEGIN
declare @sUser varchar(50),
        @sPassword varchar(32),
        @errMesaj varchar(100),
        @exists bit, 
        @exists2 bit;
set @sUser = LTRIM(RTRIM(@User))
set @sPassword = LTRIM(RTRIM(@Password))


    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select @exists =  COUNT(*) from sys.server_principals where name = @sUser
    select @exists2 = COUNT(*) from sys.database_principals where name = @sUser
    -- Insert statements for procedure here
    if @exists = 1 begin 
         if @exists2 = 1 set @errMesaj = 1 --'Userul deja exista'  
              else begin 
               exec ('CREATE USER [' + @sUser + '] FOR LOGIN [' + @sUser + ']') 
               set @errMesaj = 2  --'S-a creat userul pentru login deja existent'
               end
                   end
          else begin
                         exec(' CREATE LOGIN [' + @sUser  + '] WITH PASSWORD = ''' +@sPassword + '''
                                 CREATE USER [' + @sUser + '] FOR LOGIN [' +  @sUser+']')
                        set @errMesaj = 3 --'S-a creat user si login'
                        end

 RETURN @errMesaj   
END

This SP add a login and a user into a server/database... and returns a message based on what it has done.

In delphi I've got a TADOStoredProcedure that has 3 parameters :

1 : @User - ftString , pdInput 2 : @Password - ftString , pdInput 1 : @errMesaj - ftString , pdReturnValue

I call the TADOStoredProcedure like this :

procedure TdataModule1.AdaugaUser(user, password : string );  // procedura ce apeleaza o SP din BD pentru adaugarea de useri
begin
   sp_adaugaUser.Parameters.ParamByName('@User').Value := user;
   sp_adaugaUser.Parameters.ParamByName('@Password').Value := password;
   sp_adaugaUser.Prepared :=  true ;
   try
   sp_adaugaUser.ExecProc;
   finally
   sp_adaugaUser.Prepared :=  false ;
   sp_vizualizare_useri.Close;
   sp_vizualizare_useri.Open;
   test := sp_adaugaUser.Parameters.ParamByName('@errMesaj').Value;
   end;

end;

but for some reason when I debug the value of test is always null, why?

Error :

Answer 1

If you want to use the parameter @errMesaj as described you will have to define it with OUTPUT.

ALTER PROCEDURE [dbo].[sp_adaugaUser]
    -- Add the parameters for the stored procedure here
    @User varchar(50), 
    @Password varchar(32),
    @errMesaj varchar(100) OUTPUT
AS

The RESULT value of SQLServer procedure is limited to an integer Return Data from a Stored Procedure

An example to show the different possibilies could look like this:

Alter PROCEDURE [dbo].[sp_adaugaUser]
    -- Add the parameters for the stored procedure here
    @User varchar(50), 
    @Password varchar(32),
    @errMesaj varchar(100) OUTPUT
AS
BEGIN
declare @sUser varchar(50),
        @sPassword varchar(32),
        @ReturnCode int,   
        @exists bit, 
        @exists2 bit;
set @sUser = LTRIM(RTRIM(@User))
set @sPassword = LTRIM(RTRIM(@Password))


    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    select @exists =  COUNT(*) from sys.server_principals where name = @sUser
    select @exists2 = COUNT(*) from sys.database_principals where name = @sUser
    -- Insert statements for procedure here
    if @exists = 1 begin 
         if @exists2 = 1 
            begin
              set @errMesaj = 'Userul deja exista'  
              set @ReturnCode = 1
            end
              else begin 
               exec ('CREATE USER [' + @sUser + '] FOR LOGIN [' + @sUser + ']') 
                 set @errMesaj = 'S-a creat userul pentru login deja existent'
                 set @ReturnCode = 2
               end
                   end
          else begin
                        exec(' CREATE LOGIN [' + @sUser  + '] WITH PASSWORD = ''' +@sPassword + '''
                                 CREATE USER [' + @sUser + '] FOR LOGIN [' +  @sUser+']')
                        set @errMesaj = 'S-a creat user si login'
                        set @ReturnCode = 2
                        end

    Return @ReturnCode
END

the minimal delphi code for a call

   sp_adaugaUser.Parameters.ParamByName('@User').Value := user;
   sp_adaugaUser.Parameters.ParamByName('@Password').Value := password;
   sp_adaugaUser.ExecProc;
   Showmessage(sp_adaugaUser.Parameters.ParamByName('@errMesaj').Value);
   Showmessage(IntToStr(sp_adaugaUser.Parameters.ParamByName('@RETURN_VALUE').Value));

using a TAdoStoredProc for sp_adaugaUser your parameters should look like this

the error you are getting is because in test := sp_adaugaUser.Parameters.ParamByName('@errMesaj').Value; the value is NULL which can't be converted to a string.