Stored Procedure Execution with Native Queries By Passing Variables with Data

[nizalsha]

Hi,
I am new to Hasura and I am doing some complex operations, I need a little support to solve my problem described below.

I have a stored procedure 'sp_InserttblPhOrderSummaryWithDetails'

`SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[sp_InserttblPhOrderSummaryWithDetails]
(
@p_CustomerId as int
,@p_TotalProducts as decimal(15,5)
,@p_QuantityInStock as decimal(15,5)
,@p_OrderQuantity as decimal(15,5)
,@p_OrderValue as decimal(16,5)
,@p_OrderDate as DATETIME
,@p_OrderStatus as nvarchar(20)
,@p_CustomerName as NVARCHAR(max)
,@p_ProductDetails as nvarchar(MAX) -- Detail Table Data
,@p_Id bigint out
)
as
--exec sp_InserttblPhOrderSummaryWithDetails 100,6,3,2,123,1,'301,'''Product1'',''LT001'',''BATCH01'',''2023-12-28'',2,1,3!@#2,302,''Product2'',''LT002'',''BATCH01'',''2023-12-22'',4,2,6'
INSERT INTO [dbo].[tblPhOrderSummary]
([CustomerId]
,[TotalProducts]
,[QuantityInStock]
,[OrderQuantity]
,[OrderValue]
,[OrderDate]
,[OrderStatus]
,[CustomerName])
VALUES
(@p_CustomerId
,@p_TotalProducts
,@p_QuantityInStock
,@p_OrderQuantity
,@p_OrderValue
,@p_OrderDate
,@p_OrderStatus
,@p_CustomerName)

select @p_Id = max(id) from tblPhOrderSummary nolock
----------------- Insert @p_ProductDetails into a hash table -----S

create table #tbltempProductDetails (
	orderId bigint
,	ProdDetails nvarchar(max)
)
Insert into #tbltempProductDetails 
select @p_Id,* from [fn_Split_String_AnySCharacter] (@p_ProductDetails,'!@#')
--select 11,* from [fn_Split_String_AnySCharacter] ('1,301,''Product1'',''LT001'',''2023-12-28'',2,1,3!@#2,302,''Product2'',''LT002'',''2023-12-22'',4,2,6','!@#')

--select * from #tbltempProductDetails
----------------- Insert @p_ProductDetails into a hash table -----E

----------------- Insert ProductDetails from hash table  thru cursor -----S
declare @l_csvValues as nvarchar(max)

DECLARE ProdDtl_cursor CURSOR FOR select ProdDetails from #tbltempProductDetails where orderId = @p_Id;

OPEN ProdDtl_cursor

FETCH NEXT FROM ProdDtl_cursor
INTO @l_csvValues

WHILE @@FETCH_STATUS = 0
BEGIN

		declare @l_SqlQuery as nvarchar(max)
		select  @l_SqlQuery = 'Insert into tblPhOrderProducts (OrderId,ItemCode,ItemDescription,LotNumber,Batch,ExpiryDate,InStock,CurrentStock,orderQty) select ' + LTRIM(RTRIM(CAST(@p_Id AS VARCHAR(15)))) + ',' + @l_csvValues 
		print	@l_SqlQuery
		exec sp_executesql  @l_SqlQuery 
		 
		--select * from #tbltempProductDetails
		--select * from tblPhOrderProducts

-- Get the next product details.  
FETCH NEXT FROM ProdDtl_cursor   
INTO @l_csvValues 

END
CLOSE ProdDtl_cursor;
DEALLOCATE ProdDtl_cursor;

--Select * From (Values (1),(2),(3),(4)) #tbltempProductDetails (prodetails)
--select * from [fn_Split_String_AnySCharacter] ('1|*|301|*|Product1|*|LT001|*|btch01|*|2023-12-28|*|2|*|1|*|3','|*|')

select @p_Id as Idmax--from #tbltempProductDetails
----------------- Insert ProductDetails from hash table  thru cursor -----E

GO
`

and I added it in my native query section in hasura cloud like this to run

EXEC sp_InserttblPhOrderSummaryWithDetails {{CustomerId}},{{TotalProducts}},{{QuantityInStock}},{{OrderQuantity}},{{OrderValue}},'{{OrderDate}}',{{OrderStatus}},'{{CustomerName}}','{{ProductDetails}}',{{EndParam}};

It will be something like this with the data

exec sp_InserttblPhOrderSummaryWithDetails 100,6,3,2,123,1,'301,'''Product1'',''LT001'',''BATCH01'',''2023-12-28'',2,1,3!@#2,302,''Product2'',''LT002'',''BATCH01'',''2023-12-22'',4,2,6'

I get this in my query root and i tried to run this but got this error,

{ "errors": [ { "message": "database query error", "extensions": { "path": "$", "code": "unexpected", "internal": { "exception": { "message": "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'EXEC'.", "type": "unsuccessful_return_code" }, "query": "WITH [PhSpInserttblPhOrderSummaryWithDetails0] AS ( EXEC sp_InserttblPhOrderSummaryWithDetails 100,100,100,50,20000,'CAST(N'2023-08-05' AS NVARCHAR(MAX))',1,'CAST(N'NIZALHSA' AS NVARCHAR(MAX))','CAST(N'301,'+NCHAR(39)+''+NCHAR(39)+'Product1'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'LT001'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'BAT01'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'2023-12-28'+NCHAR(39)+''+NCHAR(39)+', 2,1,25'+NCHAR(33)+''+NCHAR(64)+''+NCHAR(35)+'302,'+NCHAR(39)+''+NCHAR(39)+'Product2'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'LT002'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'BAT02'+NCHAR(39)+''+NCHAR(39)+','+NCHAR(39)+''+NCHAR(39)+'2023-12-22'+NCHAR(39)+''+NCHAR(39)+',4,2,25' AS NVARCHAR(MAX))',-1\n ) SELECT ISNULL((SELECT [PhSpInserttblPhOrderSummaryWithDetails0].[Idmax] AS [Idmax] FROM [PhSpInserttblPhOrderSummaryWithDetails0] FOR JSON PATH, INCLUDE_NULL_VALUES), CAST(N''+NCHAR(91)+''+NCHAR(93)+'' AS NVARCHAR(MAX))) AS [root] /* field_name=PhSpInserttblPhOrderSummaryWithDetails, parameterized_query_hash=7cde42188819f2beb0640da7836cdf7ebab45738, operation_name=MyQuery */" } } } ] }

I dont know why this is happening, I am not that technically sound to address this myself if anyone can please help?

[SamirTalwar]

You will need to use the "stored procedures" feature, not the "native queries" feature. Native queries only support SELECT queries.

https://hasura.io/docs/latest/schema/ms-sql-server/logical-models/stored-procedures/