How to add parameters to a stored procedure in B1 Query generator

[Former Member]

Dear All,

I made a stored procedure in sql. I'd like to call it from B1. It's working fine when I call like this (I created an empty query in Query generator and paste this) :

exec stproc @projectFrom = '',

@projectTo = 'PRJ03',

@profitCodeFrom = '',

@profitCodeTo = 'uj',

The problem is, Could I call this procedure with parameters like normal queries (select ... where .. = [%0])? Can I pass parameters to it somehow?

Thanks a lot

Jani

[Former Member · Accepted solution]

Hello Janos,

You can do the following

{declare @param1 as <type>}

{select @param1=cardcode from OCRD where cardcode=[%0]}

{exec your_procedure @param1}

hope it helps

Kind regards

George Christodoulou

[Former Member · Accepted solution]

Thank you George.

How does it work for more than 1 parameter?

[Former Member · Accepted solution]

Hi,

declare @param1 as <type>

declare @param2 as <type>

select @param1=cardcode from ocrd where cardcode=[%0]

select @param2=itemcode from oitm where itemcode=[%1]

exec <your procedure> @param1, @param2

you must have set the parameters also in your stored procedure

Hope it helps

Georgex

[Former Member · Accepted solution]

Thanks a lot George

[Former Member · Accepted solution]

Thanks a lot George for this help.

I have a similar problem :

First the following SQL treatment is working :

declare @offrenum nvarchar(100)

set @offrenum=400

IF NOT EXISTS(Select * from [dbo].[@TS_CONDBYQUOTATION] T25 where @offrenum = T25.U_Quotation )

BEGIN

SQL treatment

END

select * from [dbo].[@TS_CONDBYQUOTATION] where U_quotation = @offrenum

This one is also working :

declare @offrenum nvarchar(100)

select @offrenum=T1.docnum from oqut T1 where T1.docnum=[%docnum]

select @offrenum from ocrd

But if I try to do this one :

declare @offrenum nvarchar(100)

select @offrenum=T1.docnum from oqut T1 where T1.docnum=[%docnum]

IF NOT EXISTS(Select * from [dbo].[@TS_CONDBYQUOTATION] T25 where @offrenum = T25.U_Quotation )

BEGIN

SQL treatment

END

select * from [dbo].[@TS_CONDBYQUOTATION] where U_quotation = @offrenum

It doesn't work ... can you help me ???

[Former Member · Accepted solution]

Hi Hugues,

Sorry but, I can't understand the query. Not the meanning, but what you have written.

Kind regards

Georgex

[Former Member · Accepted solution]

HI Hugues,

I solved my problem like this:

--SELECT T0.[Project] FROM JDT1 T0 WHERE T0.[Project] >= AND T0.[Project] <= AND T0.[ProfitCode] >= AND T0.[ProfitCode] <= AND T0.[RefDate] >= '[%4]' AND T0.[RefDate] <= '[%5]' AND T0.[DueDate] >= '[%6]' AND T0.[DueDate] <= '[%7]' AND T0.[Account] >= '[%8]' AND T0.[Account] <= '[%9]'

exec stored_proc @projectFrom = '[%0]', @projectTo = '[%1]', @profitCodeFrom = '[%2]', @profitCodeTo = '[%3]', @refDateFrom = '[%4]', @refDateTo = '[%5]', @dueDateFrom = '[%6]', @dueDateTo = '[%7]', @accountFrom = '[%8]', @accountTo = '[%9]'

I know, it seems stupid, but it's working

Edited by: János Dubin on Jun 5, 2008 9:36 AM

[Former Member · Accepted solution]

Thanks a lot George for this help.

I have a similar problem :

First the following SQL treatment is working :

declare @offrenum nvarchar(100)

set @offrenum=400

IF NOT EXISTS(Select * from [dbo].[@TS_CONDBYQUOTATION] T25 where @offrenum = T25.U_Quotation )

BEGIN

SQL treatment

END

select * from [dbo].[@TS_CONDBYQUOTATION] where U_quotation = @offrenum

This one is also working :

declare @offrenum nvarchar(100)

select @offrenum=T1.docnum from oqut T1 where T1.docnum=[%docnum]

select @offrenum from ocrd

But if I try to do this one :

declare @offrenum nvarchar(100)

select @offrenum=T1.docnum from oqut T1 where T1.docnum=[%docnum]

IF NOT EXISTS(Select * from [dbo].[@TS_CONDBYQUOTATION] T25 where @offrenum = T25.U_Quotation )

BEGIN

SQL treatment

END

select * from [dbo].[@TS_CONDBYQUOTATION] where U_quotation = @offrenum

It doesn't work ... can you help me ???

[Former Member · Accepted solution]

Thank you ,

I will try this ..

[Former Member · Accepted solution]

It works perfectly ... thanks a lot ....

Except with Date ... how do you manage date from B1 to SQl ??

Thanks in advance.

[Former Member · Accepted solution]

It should work automatically if you select a date type variable. Except if the field of the date is empty. You should handle this situation in the stored procedure. Like this:

if @dueDateFrom = ''

begin

select @dueDateFrom = (select min(duedate) from jdt1)

end

[Former Member · Accepted solution]

After a lot of tries, unfortunatly it doesn't work ...

Here is my code :

DECLARE @return_value int

Declare @Fromdate datetime

declare @Todate datetime

declare @supp nvarchar(15)

SELECT * FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry where

T0.cardcode = '[%Supp]' and T0.DocDate <=[%FromDate]

and T0.DocDate >=[%ToDate]

EXEC @return_value=SQL2XMLforINV

@Fromdate= [%FromDate],

@ToDate= [%ToDate],

@Supp = '[%Supp]'

SELECT 'Return Value' = @return_value

And i get an error : incorrect syntax neer the keyword convert .... : I don't understand !!

Please help me !!!

[Former Member · Accepted solution]

Dear Hugues,

Can you separate your stored procedure and the code in B1?

Where do you got this error message?

Regards,

Jani

[Former Member · Accepted solution]

Inside B1 :

DECLARE @return_value int

Declare @Fromdate datetime

declare @Todate datetime

declare @supp nvarchar(15)

SELECT * FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry where

T0.cardcode = '%Supp' and T0.DocDate <=%FromDate

and T0.DocDate >=%ToDate

EXEC @return_value=SQL2XMLforINV

@Fromdate= %FromDate,

@ToDate= %ToDate,

@Supp = '%Supp'

SELECT 'Return Value' = @return_value

And in SQL I have a stored procedure :

Starting like this :

PROCEDURE [dbo].[SQL2XMLforINV]

@Fromdate datetime,

@Todate datetime,

@supp nvarchar(15)

AS

DECLARE @bcpCommand AS varchar(3000)

declare @base nvarchar(50)

BEGIN

SET NOCOUNT ON;

And my error message is in B1 ....

Thanks in advance for your help ....

[Former Member · Accepted solution]

Try this one:

nside B1 :

DECLARE @return_value int

Declare @Fromdate datetime

declare @Todate datetime

declare @supp nvarchar(15)

--SELECT * FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry where

T0.cardcode = '%Supp' and T0.DocDate <='%FromDate'

and T0.DocDate >='%ToDate'

EXEC SQL2XMLforINV

@Fromdate= %FromDate,

@ToDate= %ToDate,

@Supp = '%Supp'

--SELECT 'Return Value' = @return_value

Maybe the problem was the datetime should also text type and you should put it between ' '