passing parameters to Store Procedure

• mbarr350

  SSC Enthusiast

  Points: 198

  More actions

  April 21, 2004 at 1:04 pm

  #359429

  I have a Access Project w/ SQL.

  I have created reports against a stored procedure with parameters. I pass the parameters to the SP thru an unbound form via the input parameter property of the reports.

  My question is how can I pass a range of values (ie JobNo 101 thru 110) or a series of values (ie JobNo 101,103,105,110) to the SP from the Form?

   

  Thanks

• kaeg

  Old Hand

  Points: 378

  More actions

  April 24, 2004 at 8:17 am

  #504132

  You can use IN operator to help. You might check this sp out by testing in Northwind database.

  Create Procedure spGetEmployee

  @EmployeeIDs varchar(500)

  AS

  declare @sql varchar(1000)

  select @sql = 'SELECT *

  FROM dbo.Employees

  WHERE EmployeeID IN (' + @EmployeeIDs + ')'

  exec (@sql)

  GO

  Then test it out like this.

  exec spGetEmployee "1,4,5,9"

  When you use the sp in Access Project, just add this line in InputParameters property of the report.

  @EmployeeIDs=Forms!frmYourTargetForm!TextBox0

  Type  1,4,5,9 in the TextBox0 and view the report.

  Hope this is what you want.

  

   

   

   

   

   

• Pavan H A

  SSC Rookie

  Points: 33

  More actions

  April 25, 2004 at 11:53 pm

  #504178

  It is fine.   Arguments like 1, 4, 5, 9 can be passed as

  exec spGetEmployee "1,4,5,9"

  and can be handled as

  WHERE EmployeeID IN (' + @EmployeeIDs + ')'

  But, how do we code it when we have to pass some string data as similar arguments?

  Eg. 'Kumar Sanu', 'Amit Khanna', 'Raj Sri', 'Pavan H A', 'Will Smith'

   

  
  Pavan H A