How to pass an argument to a stored procedure in SAP B1

[Former Member]

Dear SAP Community,

does anyone know the right syntax for passing an argument to a stored procedure? The reference which seems to work most is

https://archive.sap.com/discussions/thread/976079 (year 2008)

I am creating a stored procedure, then try to execute it :

CREATE PROC vssec AS 
DECLARE @Dt AS DATETIME/* SELECT FROM [dbo].[ORDR] T0 WHERE T0.DocDate = */ SET @Dt = '[%0\]' ;
EXEC vssec @Dt

When I am saving the query, a pop up window with all order dates appears; I choose a random date value from ORDR table and the SAP assigns this value to the argument. Then it is not possible to execute a procedure with a parameter

SAP saves this procedure as

CREATE PROC vssec AS 
DECLARE @Dt AS DATETIME/* 
SELECT FROM [dbo].[ORDR] T0 WHERE T0.DocDate = */ SET @Dt = CONVERT(DATETIME, '20161001', 112) 

Syntax like this is not working either:

CREATE PROC vssec AS 
DECLARE @Dt AS DATETIMESELECT * FROM [dbo].[ORDR] T0 
WHERE T0.DocDate = @Dt

Could anyone share the right syntax? I would highly appreciate any help, as well as references to the correct syntax explained. We have SAP B1 9.2 on MS SQL Server 2012

Thank you a lot in advance,

[Johan_H]

Hi Günther,

Perhaps old school syntax works:

/* SELECT * FROM ORDR x */
CREATE PROC vssec AS 
DECLARE @Dt AS DATETIME
SET @Dt = /* T0.DocDate */ [%0] ;
EXEC vssec @Dt

Regards,

Johan

[Former Member]

Unfortunately it is not - 'the table to select from is not specified'