0

I have this router in nodejs and I want to execute a stored procedure: 

router.post('/upadateCreateSiteMonth', isAuthed, function(req, res) {
        //console.log('REQ USER', req.user);
        var q = 'USE [EYE]' +
                'GO' +
                'DECLARE    @return_value int' +
                'EXEC   @return_value = [dbo].[uspCreateSiteMonthConsumption]' +
                'SELECT \'Return Value\' = @return_value' +
                'GO'
        exp.emitter.emit('mssql',{ q: q}, function(err, data) {
            if (err) {
                res.status(500).json({ error : err });  
            } else {
                res.status(200).json(data);  
            }
       });
    });

When I write this in SQL Server database it is ok

DECLARE @return_value INT
EXEC    @return_value = [dbo].[uspCreateSiteMonthConsumption]

SELECT  'Return Value' = @return_value
GO

How can I write the variable q to respond with 200 this router?

A simple query like Select * from table works ok, but when I try to use this query to run the stored procedure, I get a response of 500.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
devRena
  • 327
  • 2
  • 5
  • 12
  • Why don't you try to use Scalar Function instead of Stored Procedure – kastriotcunaku May 10 '18 at 11:47
  • Possible duplicate of [Running a stored procedure with NodeJS and MSSQL package error](https://stackoverflow.com/questions/38909920/running-a-stored-procedure-with-nodejs-and-mssql-package-error) – Panagiotis Kanavos May 10 '18 at 11:48
  • You *shouldn't* try to run anything like that. Instead of using parameterized queries you are using plain old string concatenation that exposes you to SQL Injection attacks. Whatever library you use you shouldn't have to specify more than the name of the stored procedure in the query and the parameter names and values *separately*. – Panagiotis Kanavos May 10 '18 at 11:49
  • 1
    BTW unless you care about the return value, you could just write `exec dbo.uspCreateSiteMonthConsumption` or even `uspCreateSiteMonthConsumption` – Panagiotis Kanavos May 10 '18 at 11:51
  • When i execute `exec dbo.uspCreateSiteMonthConsumption` i take error `{ RequestError: Warning: Null value is eliminated by an aggregate or other SET o peration.` – devRena May 10 '18 at 12:11
  • When I execute `DECLARE @return_value int EXECUTE @return_value = [dbo].[uspCreateSiteMonthConsumption];` i take error `{ RequestError: Incorrect syntax near '@return_value'. at StreamEvents. (c:\eye.v3\node_modules\mssql\lib\msnodesqlv8.js :624:21)` – devRena May 10 '18 at 12:13
  • I executed `SET ansi_warnings off EXEC [dbo].[uspCreateSiteMonthConsumption]` now i don't have the same error – devRena May 10 '18 at 14:24

0 Answers0