displaying RAISERROR message in C++ application (!!!)

[saduc]

I would be gratefull if someone can help me with this problem which borders me a lot:
(I simplified my solution to test procedure)

I have this stored procedure:
CREATE PROCEDURE test ( @id varchar(100))
AS
RAISERROR('Error message',15,1)

I have application written in C++ using ADO
I catch error messages (as shown at the bottom) and display its description
this is what happens to me:
- if the procedure test does not raise error, everything is OK
- if the procedure test is without parameters, the message I am rising in it is displayed
- BUT: if the procedure test has parameters and raises error, I always get:
"err:[Microsoft][ODBC SQL Server Driver][SQL Server]procedure 'test' expects parameter '@id', which was not supplied."
Sure - I need to get the message I raised, not this one !!
If I try calling the procedure under the Query analyzer, everyting works fine.
Can anyone help me, please ?

part of calling code:
pCommand->CommandType = adCmdStoredProc;
pCommand->CommandText = "test";
pCommand->Name= "test";
_ParameterPtr param;
param.CreateInstance(CLSID_Parameter);
param->Direction=adParamInput;
param->Name="@id";
param->Type=adBSTR;
param->Value=_bstr_t("22"
param->Size=3;

pCommand->Parameters->Append(param);

_variant_t vNull;
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;

pCommand->ActiveConnection = pConnection;
pCommand->Execute( &vNull, &vNull, adCmdUnknown );

} catch (_com_error &e) {
printf("err:%s\n",(LPCSTR)e.Description());
}

 

[tlbroadbent]

Change your stored procedure to provide default value for @id. Then test for that value. If true then raise error.

Example:

CREATE PROCEDURE test @id varchar(100)='No parameter' AS

If @id = 'No parameter'
RAISERROR('Error message',15,1)
Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[saduc]

well, thanks - but it does not work exactely in the way I need if the test is like this:

CREATE PROCEDURE test @id int=0 AS
If @id>100 RAISERROR('@id>100',16,1)

I tried the same to rewrite into ODBC calls - and it works, but I do not want to rewrite all the project from ADO to ODBC - it would be pretty nasty.

 

[tlbroadbent]

You have three possible conditions.

1- No parameter is provide in the SP call.
2- The parameter is out of range.
3- The paramter is correct.

You can write the SP as follows to handle the three conditions.

CREATE PROCEDURE test @id int=null
AS

If @id = Null
Begin
RAISERROR('Parameter @id required but not provided.',15,1)
Return @@error
End
Else
If @id > 100
Begin
RAISERROR('Parameter @id > 100.',15,1)
Return @@error
End

/* @id is within range - proceed with remainder of procedure */

.
.
. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[saduc]

Sorry, but you did not probably get the right idea of my problem.

Byt - after lets say 12 hours spent during the night on that problem, I solved it by revriting the application to the 'SQLOLEDB' provider,
adding SET NOCOUNT ON command at the start of each stored procedure which works with cursors
and rearranging some parameters to the Command object.

Well, I am litle bit confused about SQL Server developers right now, it seems to me that there is a lot of mess in the SQL Server...

 

[tlbroadbent]

Glad you found a solution. I'm sure I still don't understand the issues or or problems. Good luck. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm