Recent experience in using SQL Server 2000

The following errors always occur during the afternoon when the database is used: syntax error converting the nvarchar value to a column of Data Type int is found at last because the return value in the stored procedure can only be a simple int type, which wastes a lot of time, now let's summarize the knowledge we 've encountered.
1. Execute an output-type stored procedure in the stored procedure. The following is an example of sp dbo. dt_displayoaerror that comes with sqlserver:
Exec @ hR = sp_oageterrorinfo @ iobject, @ vchsource out, @ vchdescription out
@ HR gets the return value of sp. @ iobject is the input value, @ vchsource, @ vchdescription is the output value of SP.

2. in the database, if the value is not null, it is different from ''. Unlike some languages, the value of null is assigned to variable types. therefore, for a null variable, it must be determined by @ temp = NULL, or the isnull function is used. the syntax of isnull is isnull ( Check_Expression, Replacement_value)IfCheck_ExpressionIf it is null, replace it with the second parameter.

3. return Value and output value of the stored procedure. the return value can only be a numeric value. However, if a variable of the nvarchar type and a number in the variable can be implicitly converted to a number, the return value can be used as the return value. similarly, the same applies to other types of variables.

By default, the return value of a stored procedure is 0, indicating that the stored procedure is successfully executed. If it is a non-zero value, the execution is not successful. we can think of the returned data that is useful to ourselves. For example, when a new user is added, the userid of the new user is returned. use: Return @ usr_id, and then use

Due to the deficiency of return value, we can return data through recrodset and output.
The following is Microsoft's help information

Sqlcommand samplecmd = new sqlcommand ("sampleproc", nwindconn );
Samplecmd. commandtype = commandtype. storedprocedure;

Sqlparameter sampparm = samplecmd. Parameters. Add ("return_value", sqldbtype. INT );
Sampparm. Direction = parameterdirection. returnvalue;

Sampparm = samplecmd. Parameters. Add ("@ inputparm", sqldbtype. nvarchar, 12 );
Sampparm. value = "sample value"

Sampparm = samplecmd. Parameters. Add ("@ outputparm", sqldbtype. nvarchar, 28 );
Sampparm. Direction = parameterdirection. output;
Nwindconn. open ();
Sqldatareader sampreader = samplecmd. executereader ();
Console. writeline ("{0}, {1}", sampreader. getname (0), sampreader. getname (1 ));
While (sampreader. Read ())
{
Console. writeline ("{0}, {1}", sampreader. getint32 (0), sampreader. getstring (1 ));
}
Sampreader. Close ();
Nwindconn. Close (); // note that the return value and output valve can be returned only after the datareader is closed.
Console. writeline ("@ outputparm: {0}", samplecmd. Parameters ["@ outputparm"]. value );
Console. writeline ("return_value: {0}", samplecmd. Parameters ["return_value"]. value );

In SP, You need to declare external parameters before as. If the output type is used, the Declaration. Created by jecray must be displayed.