Board index » delphi » problem deploying stored proc with ADO

problem deploying stored proc with ADO

I've been given a script to create a stored proc.
I try to run it in an ADO query and I get the message
'Parameter object is improperly defined.  Inconsisten or incomplete
information was provided'

I have not clue what that means.  I haven't set any parameters in the query,
since I don't need any.
Below is the stored proc.  Can somebody help me?

----------------------------------------------------------------------------
-------
create procedure sp_getKey( @key_name varchar(256), @key_count integer
   , @first_key_value varchar(40) output
   , @keys_allocated_count int = NULL output
   , @data_type varchar(256)= NULL output
   ) with recompile
as

DECLARE
  @err_msg nvarchar(255),
  @err_num integer ,
  @rows integer ,
  @precision integer ,
  @max_precision integer ,
  @next_key_precision integer

  SET @precision = 32
  SET @keys_allocated_count = 0

  if (@key_count is NULL or @key_count < 1)
  begin
    select @err_msg = 'Invalid key_count specified, key_count has to be
greater than zero'
    select @err_num = 13002
    goto error
  end

  begin tran

    update keys
    set last_key_value =  CASE data_type
    WHEN 'uniqueidentifier' THEN convert( varchar(36), newid())
    ELSE convert( varchar(36) , convert( decimal( 32 , 0) , last_key_value )
+ @key_count )
     END
    where key_name = @key_name

    select @err_num = @@error, @rows = @@rowcount

    if @err_num <> 0
    begin
      select @err_msg = description, @err_num = 13002
      from master.dbo.sysmessages
      where error = @err_num
      rollback tran
      goto error
    end

    if @rows <> 1
    begin
      select @err_msg = 'No rows found', @err_num = 13002
      rollback tran
      goto error
    end

    select @first_key_value = CASE data_type
                                   WHEN 'uniqueidentifier' THEN
                                      last_key_value
                                   ELSE
                                       ( right (  replicate('0',
max_precision)
                                                  + convert(varchar(32),
                                                      ( convert( decimal( 32
, 0) , last_key_value ) - @key_count + 1)
                                                           )
                                               , max_precision
                                               )
      )

       END
            , @max_precision = max_precision
            , @next_key_precision = CASE WHEN data_type <>
'uniqueidentifier'
                                             THEN len( convert(varchar(32),
convert( decimal( 32 , 0) , last_key_value ) ) )
                                         END
            , @data_type = data_type
    from keys
    where key_name = @key_name

    select @err_num = @@error
    if @err_num <> 0
    begin
      select @err_msg = description, @err_num = 13002
      from master.dbo.sysmessages
      where error = @err_num
      rollback tran
      goto error
    end

    if @data_type <> 'uniqueidentifier'
    begin
       SET @keys_allocated_count = @key_count
       if @next_key_precision > @max_precision
       begin
         select @err_msg = 'Maximum precision assigned to the key
(keys.max_precision) has been exceeded' , @err_num = 13002
         rollback tran
         goto error
       end
    end
    else
    begin

       SET @keys_allocated_count = 1

    end

  commit tran
  return 0

error:
  raiserror @err_num @err_msg
  return -1

grant execute on sp_getKey to videoEmail_admin

-------------------------------------------------------------------------

 

Re:problem deploying stored proc with ADO


Use the TADOCommand component instead. ADO is really only split into three
main classes, a connection class (Connection), a dataset-like class
(Recordset) and a command processor (Command). You can still use the Command
class to return a Recordset, but Command has the added ability of processing
DDL statements.

Quote
"Casper Milquetoast" <cas...@milquetoast.com> wrote in message

news:3bf04e86_1@dnews...
Quote
> I've been given a script to create a stored proc.
> I try to run it in an ADO query and I get the message
> 'Parameter object is improperly defined.  Inconsisten or incomplete
> information was provided'

> I have not clue what that means.  I haven't set any parameters in the
query,
> since I don't need any.
> Below is the stored proc.  Can somebody help me?

> --------------------------------------------------------------------------
--
> -------
> create procedure sp_getKey( @key_name varchar(256), @key_count integer
>    , @first_key_value varchar(40) output
>    , @keys_allocated_count int = NULL output
>    , @data_type varchar(256)= NULL output
>    ) with recompile
> as

> DECLARE
>   @err_msg nvarchar(255),
>   @err_num integer ,
>   @rows integer ,
>   @precision integer ,
>   @max_precision integer ,
>   @next_key_precision integer

>   SET @precision = 32
>   SET @keys_allocated_count = 0

>   if (@key_count is NULL or @key_count < 1)
>   begin
>     select @err_msg = 'Invalid key_count specified, key_count has to be
> greater than zero'
>     select @err_num = 13002
>     goto error
>   end

>   begin tran

>     update keys
>     set last_key_value =  CASE data_type
>     WHEN 'uniqueidentifier' THEN convert( varchar(36), newid())
>     ELSE convert( varchar(36) , convert( decimal( 32 , 0) ,
last_key_value )
> + @key_count )
>      END
>     where key_name = @key_name

>     select @err_num = @@error, @rows = @@rowcount

>     if @err_num <> 0
>     begin
>       select @err_msg = description, @err_num = 13002
>       from master.dbo.sysmessages
>       where error = @err_num
>       rollback tran
>       goto error
>     end

>     if @rows <> 1
>     begin
>       select @err_msg = 'No rows found', @err_num = 13002
>       rollback tran
>       goto error
>     end

>     select @first_key_value = CASE data_type
>                                    WHEN 'uniqueidentifier' THEN
>                                       last_key_value
>                                    ELSE
>                                        ( right (  replicate('0',
> max_precision)
>                                                   + convert(varchar(32),
>                                                       ( convert(
decimal( 32
> , 0) , last_key_value ) - @key_count + 1)
>                                                            )
>                                                , max_precision
>                                                )
>       )

>        END
>             , @max_precision = max_precision
>             , @next_key_precision = CASE WHEN data_type <>
> 'uniqueidentifier'
>                                              THEN len(

convert(varchar(32),

- Show quoted text -

Quote
> convert( decimal( 32 , 0) , last_key_value ) ) )
>                                          END
>             , @data_type = data_type
>     from keys
>     where key_name = @key_name

>     select @err_num = @@error
>     if @err_num <> 0
>     begin
>       select @err_msg = description, @err_num = 13002
>       from master.dbo.sysmessages
>       where error = @err_num
>       rollback tran
>       goto error
>     end

>     if @data_type <> 'uniqueidentifier'
>     begin
>        SET @keys_allocated_count = @key_count
>        if @next_key_precision > @max_precision
>        begin
>          select @err_msg = 'Maximum precision assigned to the key
> (keys.max_precision) has been exceeded' , @err_num = 13002
>          rollback tran
>          goto error
>        end
>     end
>     else
>     begin

>        SET @keys_allocated_count = 1

>     end

>   commit tran
>   return 0

> error:
>   raiserror @err_num @err_msg
>   return -1

> grant execute on sp_getKey to videoEmail_admin

> -------------------------------------------------------------------------

Other Threads