cancel
Showing results for 
Search instead for 
Did you mean: 

how to pass a variable to SP from querry generator in SAP b1

Former Member
0 Kudos

hi experts,

i am able execute the following query ,

exec sp_Attendance_Report '09','2010'

actually i need to pass month and year as variable to SP ,how to do that

i tried with the following

exec sp_Attendance_Report '[%0]','[%1]'

i m getting the following error message

1). [Microsoft][SQL Native Client][SQL Server]Must specify table to select from.
2). [Microsoft][SQL Native Client][SQL Server]Statement 'Service Contracts' (OCTR) (s) could not be prepared.

please help me to solve this issue

thanks in advance

ajith

Accepted Solutions (1)

Accepted Solutions (1)

former_member201110
Active Contributor
0 Kudos

Hi Ajith,

There is a little trick you can do to fool SBO in to providing a proper parameter prompt when a user runs the query:

1) Create a new user-defined table

2) Create 2 new user-defined fields on the table with data type numeric and the UDF description 'Year' and 'Month'

3) Write your query as follows:


/*SELECT FROM [dbo].[@YEARMONTH] T0*/ 
declare @Year as int /* WHERE */ 
set @Year = /* T0.U_Year */ '[%0]'  
/*SELECT FROM [dbo].[@YEARMONTH] T0*/ 
declare @Month as int /* WHERE */ 
set @Month = /* T0.U_Month */ '[%1]'  
exec sp_Attendance_Report '[%0]','[%1]'

4) (Optional) You can populate your UDT with possible values so the user can select from a list for each parameter. If you don't populate the table then the query can still be run but a user can't do a lookup to select from a year or month.

Kind Regards,

Owen

Former Member
0 Kudos

hi Owen Slater ,

thank you for reply

still i couldn't make out,can you explain bit more

actually i didn't get what exactly we are doing in the query which you mentioned above

please help me ,if u don't mind

thanks for your time

ajith

Former Member
0 Kudos

Hello Guys, ajith464

To understand

You must provide a system or user table to select the parameters (parameter window) then you can read the parameters into local variables. We are hiding the result of the system / user defined table selection with sql comments

you need 2 parameters: year and month. In Owen example, he has defined UTDs to hold these values, U_Year for year and U_Month for month. .Basically the paramerter values assigned to year and month. The table can be empty or can contain the possible parameters.

His code a bit cleaner:

/*SELECT * FROM [dbo].[@YEARMONTH] T0 where T0.U_Year  =  '[%0]'   and To.U_Month = '[%1]' / 
declare @Month as int 
declare @Year as int
set @Year = '[%0]' 
set @Month= '[%1]'
exec sp_Attendance_Report '[%0]','[%1]'

You can use any system tables for forcing the client to bring up the selection window of year, month.

This is a very good case to be use the booking periods as selection parameters and you may read the month and year from this table (if you have a monthly period defined).

Regards

János

Edited by: János Nagy on Sep 10, 2010 9:51 AM

Answers (2)

Answers (2)

Former Member
0 Kudos

thank you so much to all,

this forum is too helpful ,i thank you all once again

Former Member
0 Kudos

Hi Ajith,

Try to declare a parameter first to see.

Thanks,

Gordon