Replies: 1 comment
-
You will need to use the "stored procedures" feature, not the "native queries" feature. Native queries only support https://hasura.io/docs/latest/schema/ms-sql-server/logical-models/stored-procedures/ |
Beta Was this translation helpful? Give feedback.
0 replies
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
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)
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
END
CLOSE ProdDtl_cursor;
DEALLOCATE ProdDtl_cursor;
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?
Beta Was this translation helpful? Give feedback.
All reactions