# Could not find stored procedure error with mssql-plus node

**URL:** <https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396>\
**Category:** General\
**Created:** [29 September 2022 15:20 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396 "2022-09-29T15:20:08Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [29 September 2022 15:20 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/1 "2022-09-29T15:20:08Z")

</div>

Hello Everyone,

I have a problem connecting to a stored procedure. I have also found other topics with similar problem. I went through those topics as well. But i could not find any mistakes on my configuration. May be i am still missing something that i could not simply notice it.

Also i am looking for a way to store a returned value from a store procedure, and forward this value as an output parameter from mssql-plus node to other nodes in the flow.

 ![stored_procedure](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/d/ddea28e94088316d784d9c4f04f812e87a4b21d4.png)

Any help would be very helpful.

Best Regards,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [29 September 2022 15:36 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/2 "2022-09-29T15:36:37Z")

</div>

> [@Nirajan](#):
>
> I have a problem connecting to a stored procedure

What is the problem? Are you getting an error? what error?

> [@Nirajan](#):
>
> Also i am looking for a way to store a returned value from a store procedure, and forward this value as an output parameter from mssql-plus node to other nodes in the flow.

Use a debug node to see where the return value is given then use that in the parameter list of the 2nd query.

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [29 September 2022 16:06 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/3 "2022-09-29T16:06:36Z")

</div>

Hi Steve,  
I am only getting "could not find stored procedure dbo.sp\_test" error.

But if i set the editor in "Query" mode and pass the parameters by calling the stored procedure like -\> dbo.sp\_test @serialNumber, @iccid  
then it works. Please let me know if you are looking for more details.

Best Regards,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [29 September 2022 17:00 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/4 "2022-09-29T17:00:12Z")

</div>

try `sp_test` without `dbo.`

and / or, try the fully qualified name `[database].[dbo].sp_test` (where `database` is the name of the SQL database)

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 07:30 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/5 "2022-09-30T07:30:13Z")

</div>

I tried only with `sp\_test´, also and / or with the fully qualified names:

1. ´[database].[dbo].sp\_test ´
2. ´[database].[dbo].[sp\_test] ´

I am still getting the following error:  
 ![error](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/9/f9500627c7807d734a85b7aa4bb9e8c97b5e45cb.png)

Do you have other suggestions or recommendation

Best Regards,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 September 2022 07:31 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/6 "2022-09-30T07:31:16Z")

</div>

> [@Nirajan](#):
>
> - ´[database].[dbo].sp\_test ´
> - ´[database].[dbo].[sp\_test] ´

if your database really called `database`?

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 07:49 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/7 "2022-09-30T07:49:16Z")

</div>

No, my database is named 'IoT' and i have replaced the word 'database' with 'IoT'. Like:

- ´[IoT].[dbo].sp\_test ´
- ´[IoT].[dbo].[sp\_test] ´

Best Regards,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 September 2022 08:58 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/8 "2022-09-30T08:58:40Z")

</div>

I am having bother with my SQL Server so cannot help further at this moment (unless you can grant access to your DB)

For now, go with "query mode" Query - and enter the params explicity e.g. `exec dbo.MySP @p1, @p2;` It is still safer than mustache templating.

I'll try to get my SQL Server instance up and running later.

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 10:28 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/9 "2022-09-30T10:28:05Z")

</div>

Hi Steve,  
Thank you for your reply. Please let me know if you come with some other recommendations once you test it on your SQL serve instance running.

Also about my other query, can you please provide an example to get the return value from a stored procedure and forward it to other nodes? I checked that the returned value on the debug node is always 'null'.

I am not sure if i am doing in a right way. This is how i did:  
I defined an output parameter 'returnValue' as 'int' in mssql-plus node, then i passed this parameter to the stored procedure together with other parameters like:

exec [IoT].[dbo].[sp\_test]@serialNumber,@iccid,@returnValue

The 'output' property set from the database is null.

![returnValue](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/9/59997862ba3db292ddb6ec77f1bd390b007efed0.png)

Best Regards,

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 September 2022 10:36 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/10 "2022-09-30T10:36:14Z")

</div>

> [@Nirajan](#):
>
> I am not sure if i am doing in a right way. This is how i did:  
> I defined an output parameter 'returnValue' as 'int' in mssql-plus node, then i passed this parameter to the stored procedure together with other parameters like:
> 
> exec [IoT].[dbo].[sp\_test]@serialNumber,@iccid,@returnValue
> 
> The 'output' property set from the database is null.

Will need more info. Can you share the SQL Procedure as a `create procedure` script?

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 11:18 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/11 "2022-09-30T11:18:01Z")

</div>

Here is the screenshot of it,

![create_stored_proc](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/9/e9c277b297ed73f818ac7eabf07f261150225fc4.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [30 September 2022 15:19 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/12 "2022-09-30T15:19:16Z")

</div>

In which case, you can specify an output parameter in the editor.

- add a 3rd parameter to the Params UI
- set it to an output parameter
- set its name to returnValue
- set its type to int

  

or do it in the query...

```auto
DECLARE @returnValue INT;

EXEC sp_test @p1, @p2, @returnValue = @returnValue OUTPUT;

SELECT @returnValue AS 'count';

```

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 15:26 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/13 "2022-09-30T15:26:15Z")

</div>

Hi Steve,

That's awesome. Both ways work perfectly. You just made my day. It was troubling me for a whole day. Thanks a lot.

Best Regards,

---

<div class="post-metadata">

**Author:** ![Nirajan](https://avatars.discourse-cdn.com/v4/letter/n/6bbea6/32.png) [@Nirajan](https://discourse.nodered.org/u/Nirajan)\
**Post date:** [30 September 2022 15:34 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/14 "2022-09-30T15:34:34Z")

</div>

And also regarding the problem with 'cannot find stored procedure' error, if you come up with any solutions, then please kindly let me know. I would be glad to learn that as well.

Many thanks.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [14 October 2022 15:34 UTC](https://discourse.nodered.org/t/could-not-find-stored-procedure-error-with-mssql-plus-node/68396/15 "2022-10-14T15:34:51Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.