Win10 + node.js + SQL Server 2019 Express で Tedious StoredProcedure を少し試してみる

Win10 + node.js + SQL Server 2019 Express で Tedious StoredProcedure を少し試してみる
#目的
・[tedious/examples/storedProcedureWithParameters.js](https://github.com/tediousjs/tedious/blob/master/examples/storedProcedureWithParameters.js)
を参考に少し試してみる
対象はWin10 + node.js -> Ubuntu 18.04 + SQL Server 2019 Express 

##対象テーブル
データの並びは以下に準拠
<a href="http://www.post.japanpost.jp/zipcode/dl/readme.html">郵便番号データの説明</a>
対象データにユニークキーになりそうなデータが無いので
8桁のテキストを追加する

```sql
CREATE TABLE [dbo].[ZIPCODE](
	[SEQ] [nchar](8) NOT NULL,
	[PREFCODE] [nchar](3) NULL,
	[KUBUNCODE] [nchar](8) NULL,
	[POSTAL5] [nchar](5) NULL,
	[POSTAL] [nchar](8) NULL,
	[PREFKANA] [nchar](20) NULL,
	[CITYKANA] [nchar](40) NULL,
	[ADDRKANA] [nchar](80) NULL,
	[PREFKANJI] [nchar](20) NULL,
	[CITYKANJI] [nchar](40) NULL,
	[ADDRKANJI] [nchar](80) NULL,
	[FLG1] [int] NULL,
	[FLG2] [int] NULL,
	[FLG3] [int] NULL,
	[FLG4] [int] NULL,
	[FLG5] [int] NULL,
	[FLG6] [int] NULL
) ON [PRIMARY]
```
## サンプル Stored Procedure

```sql
USE DEMO; 
GO 
CREATE PROCEDURE GETZIPCODE
 @SEQ        NCHAR(8),
 @OPREFCODE  NCHAR(3)  OUTPUT,
 @OPOSTAL    NCHAR(8)  OUTPUT,
 @OCITYKANJI NCHAR(40) OUTPUT,
 @OADDRKANJI NCHAR(80) OUTPUT
AS  
SELECT @OPREFCODE  = PREFCODE
     , @OPOSTAL    = POSTAL
     , @OCITYKANJI = CITYKANJI
     , @OADDRKANJI = ADDRKANJI
FROM ZIPCODE 
WHERE SEQ = @SEQ;
GO

```
## 動作確認

```sql
 DECLARE @OPREFCODE  NCHAR(3)
 DECLARE @OPOSTAL    NCHAR(8)
 DECLARE @OCITYKANJI NCHAR(40)
 DECLARE @OADDRKANJI NCHAR(80)

EXECUTE GETZIPCODE N'00000001'
      , @OPREFCODE  OUTPUT
	  , @OPOSTAL    OUTPUT
	  , @OCITYKANJI OUTPUT
	  , @OADDRKANJI OUTPUT

SELECT  @OPREFCODE, @OPOSTAL, @OCITYKANJI, @OADDRKANJI
```

##サンプルコード

```js
var Connection = require('tedious').Connection; 
var Request = require('tedious').Request;
var TYPES = require('tedious').TYPES;

var connectionConfig = {
    userName: 'demo',
    password: 'demo',
    server: '192.168.5.49',
    options: {
        database: 'demo'
    }
};

var connection = new Connection(connectionConfig);

connection.on('connect', function(err) {  
    if (err) {
        console.log(err);
        console.log('err:connect');
        connection.exit;
        process.exit(0);
    }
    console.log("Connected");
        if (err) {
            console.log(err);
            console.log('err:request');
            process.exit(0);
        }

    execstoredProcedure();
});

function execstoredProcedure() {

    request = new Request("GETZIPCODE", function(err, rowCount) {
        if (err) {
            console.log(err);
            connection.exit;
            process.exit(0);
        }
    });

    request.on('doneInProc', function (rowCount, more, rows) {  
        console.log('doneInProc: '+ rowCount + ' row(s) returned'); 
    });

    request.on('returnValue', function(paramName, value, metadata) {
        console.log('returnValue: ' + paramName + ' : ' + value);
    });

    request.on('requestCompleted', function (err) { 
        console.log("requestCompleted: execstoredProcedure");
        connection.exit;
        process.exit(0);
    });
    // The name should not start '@'.
    request.addParameter('SEQ', TYPES.NVarChar, '00000001');
    request.addOutputParameter('OPREFCODE',  TYPES.NVarChar);
    request.addOutputParameter('OPOSTAL',    TYPES.NVarChar);
    request.addOutputParameter('OCITYKANJI', TYPES.NVarChar);
    request.addOutputParameter('OADDRKANJI', TYPES.NVarChar);

    // Call a stored procedure represented by request.
    connection.callProcedure(request);
};
```
#参考にしたのは以下のサイト
[tedious/examples/storedProcedureWithParameters.js](https://github.com/tediousjs/tedious/blob/master/examples/storedProcedureWithParameters.js)
[Tedious Request](https://tediousjs.github.io/tedious/api-request.html)
[Package - mssql-tedious-int64](https://developer.aliyun.com/mirror/npm/package/mssql-tedious-int64)
[Win10 + node.js から SQL Server 2019 Express に CSVファイルを書き込んでみる](https://qiita.com/tabizou/items/a56bba5162614d381cb6)
[Win10 + node.js から SQL Server 2019 Express に CSVファイルを書き込んでみる 修正版](https://qiita.com/tabizou/items/759105b271c419fe6683)
[Windows 10 Pro x64 + Excel2016 x86 VBA + ADODB.Command を使って SQL Server 2016 Express でストアドを試してみる](https://qiita.com/tabizou/items/2880725ef8e8244373d9)