Sequelize sytax for calling stored procedure with input parameters using MSSQL

Question

I'm getting an error for the code below and cannot find accurate information for the syntax to call a MSSQL store procedure through sequelize. I have also tried the syntx from the other posts on stackoverflow similar to CALL spcName(param1, ...)

error:

(node:14580) UnhandledPromiseRejectionWarning: Unhandled promise rejection (rejection id: 1): SequelizeDatabaseError: Incorrect syntax near '@param1'.

for code:

await sequelize.query('CALL spcName(@param1, @param2, @param3, @param4);', [value1, value2, value3, value4])

Kentonbmax · Accepted Answer · 2017-03-08T20:21:15.037

8

Code looks like this and works for MSSQL. Wanted to show date example because of formatting:

return await sequelize.query('scpTest :inDate', {replacements: {inDate: moment().subtract('d', 4).toISOString(), type: sequelize.QueryTypes.SELECT}

Following the question example:

await sequelize.query('spcName :param1, :param2, :param3, :param4)', {replacements: {param1: value, param2: value, param3: value, param4: value}, type: sequelize.QueryTypes.SELECT})

edited Mar 08 '17 at 20:21

answered Mar 01 '17 at 18:02

Kentonbmax

938
1
10
16

FYI - I have logged an issue with Sequelize regarding the query for stored procedures returning duplicate result sets. – Kentonbmax Mar 03 '17 at 14:42
Also logged an issue where my stored procedure calls are returning duplicate results. Meaning an array of arrays of results or [[results], [results]] – Kentonbmax Mar 06 '17 at 20:16
Addition of the QueryTypes.SELECT fixes the duplicate results issue. – Kentonbmax Mar 08 '17 at 20:41

score 1 · Answer 2 · answered Mar 02 '17 at 02:26

1

You can try

await sequelize.query('CALL spcName(:params )', { replacements: {params : ['value1, value2, value3, value4']} })

answered Mar 02 '17 at 02:26

Keval

3,246
20
28

hmm. It is an sql error not related to sequelize http://stackoverflow.com/a/19763826/4583460 – Keval Mar 02 '17 at 13:43
I did not copy and paste anything. Should the params array of values be a string? – Kentonbmax Mar 02 '17 at 14:36
hmm, i didnt consider that sequelize could be inserting some thing improper, is it logging any sql – Keval Mar 02 '17 at 15:23

score 0 · Answer 3 · edited Mar 23 '18 at 21:10

Use : to indicate parameter and replacements to map values. See example below.

   async getPlayers(UserId: number, requestObject: any): Promise<any> {
        this.sequelize = Server.sequelize;
        try {
            let o_rowcnt = 0;
            let playersList = await this.sequelize.query('CALL sp_discovery (:p_user, :p_game, :p_platform, :p_headphone,:p_start,:p_end , :p_stanceId , @o_rowcnt )',
                {
                    replacements: { 
                        p_user: UserId, 
                        p_game: requestObject.gameid,
                        p_platform: requestObject.platformid,
                        p_headphone:  requestObject.headphone,
                        p_start: requestObject.index,
                        p_end: requestObject.limit,
                        p_stanceId: requestObject.stanceid 
                    }
                }
            );
            return playersList;
        } catch (e) {
            return e;
        }
    }

You need to explain your solution instead of simply posting the code. — Xpleria, Oct 19 '17 at 10:37

Sequelize sytax for calling stored procedure with input parameters using MSSQL

3 Answers3

Linked