Problem in Passing Values to a StoreProcedure

• • Reply

  12 years ago

  by royal ludhiana

  Royal Ludhiana

  Toronto, Canada

  Joined 14 years ago

  Hi all,

  Situation Is:

  - On my web form I have locations to choose from. If I select a single location(Location Code is passed for the same) my store procedure works fine. But when I try to pass more than one Location(location Code) it doesn't work.

  - The parameter in my store procedure is of navarchar(50) data type.

  Store procedure:

  SELECT TOP (100) PERCENT dbo.tbEmpPersonalInfo.pkEmpCd, dbo.tbEmpPersonalInfo.EmpName

  FROM dbo.tbEmpPersonalInfo INNER JOIN

  dbo.tbEmpPaymentInfo ON dbo.tbEmpPersonalInfo.pkEmpCd = dbo.tbEmpPaymentInfo.fkEmpCd INNER JOIN

  dbo.tbEmpDeptInfo ON dbo.tbEmpPaymentInfo.fkEmpCd = dbo.tbEmpDeptInfo.fkEmpCd

  WHERE (dbo.tbEmpPaymentInfo.EligbeOverTime = 1) AND (dbo.tbEmpDeptInfo.fkLocationCd IN (@Location))

  ORDER BY dbo.tbEmpPersonalInfo.pkEmpCd

   

  I have tried passing location as '1','2', but it failed. 

  so, what can i do to perform this operation?

  Regards,

  Royal

  Report abuse
• • Reply

  12 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 years ago

  Hi Royal

  In this scenerio you need to manipulate your T-SQL as string and then you execute that SQLstring using

  EXEC

  DECLARE @strSQL AS nvarchar(2000)

  SET @strSQL='
  SELECT TOP (100) PERCENT dbo.tbEmpPersonalInfo.pkEmpCd, dbo.tbEmpPersonalInfo.EmpName
  FROM dbo.tbEmpPersonalInfo INNER JOIN
  dbo.tbEmpPaymentInfo ON dbo.tbEmpPersonalInfo.pkEmpCd = dbo.tbEmpPaymentInfo.fkEmpCd INNER JOIN
  dbo.tbEmpDeptInfo ON dbo.tbEmpPaymentInfo.fkEmpCd = dbo.tbEmpDeptInfo.fkEmpCd
  WHERE (dbo.tbEmpPaymentInfo.EligbeOverTime = 1) AND (dbo.tbEmpDeptInfo.fkLocationCd IN (' + @Location + ')) ORDER BY dbo.tbEmpPersonalInfo.pkEmpCd'

  EXEC (@strSQL)
  

  Good Luck

  Hari K......

  Report abuse
• • Reply

  12 years ago

  by khari6579

  Hari K

  Chennai, India

  Joined 15 years ago

  Hi

  This link also may helpful for you

  http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

  Hari K......

  Report abuse