Stored Procedure For SQL Query RSS

• • Reply
  

  geniusvishal

  Star

  14306 Points

  2820 Posts

  Stored Procedure For SQL Query

  Feb 25, 2012 06:29 AM|LINK

  I want help for writing the stored procedure for the following Query:

  select PSCNAME as PC
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
  union
  select PCNAME
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId

  So far i have done this:

  create proc tblProperty_SelectProperty
  @iPCId int
  select PSCNAME as PC
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
  where tblPropertyCategory.PCId=tblPropertySubCategory.PCId and
  tblPropertyCategory.PCId=@iPCId
  union
  @iPCId int
  select PCNAME
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
  where tblPropertyCategory.PCId=tblPropertySubCategory.PCId and
  tblPropertyCategory.PCId=@iPCId

  which is generating error near both of the select statement...

  Plz suggest some stored procedure for the above query..

  Thanx and Regards

  Vishal Ranjan
  
  My Website
  www.dotnetvishal.com
  
• • Reply
  

  waelouf

  Member

  250 Points

  41 Posts

  Re: Stored Procedure For SQL Query

  Feb 25, 2012 06:57 AM|LINK

  Try This:

  CREATE PROCEDURE [dbo].[tblProperty_SelectProperty] @iPCId INT
  AS
      BEGIN
          SELECT  PSCNAME AS PC
          FROM    tblPropertyCategory
                  INNER JOIN tblPropertySubCategory ON tblPropertyCategory.PCId = tblPropertySubCategory.PCId
          WHERE   dbo.tblPropertyCategory.PCId = @iPCId
          UNION
          SELECT  PCNAME AS PC
          FROM    tblPropertyCategory
                  INNER JOIN tblPropertySubCategory ON tblPropertyCategory.PCId = tblPropertySubCategory.PCId
          WHERE   dbo.tblPropertyCategory.PCId = @iPCId
      END
  GO

  • Marked as answer by Mikesdotnetting on Feb 25, 2012 11:27 AM
• • Reply
  

  geniusvishal

  Star

  14306 Points

  2820 Posts

  Re: Stored Procedure For SQL Query

  Feb 25, 2012 07:46 AM|LINK

  Thanx Bro u are right.... The Stored Procedure would be like this:

  create Procedure tblProperty_SelectProperty
  --Your Parameter Goes Here
  @iPCId INT
  AS
  select PSCName as PC --Virtual Table named PC which will be used for Storing the Union Data
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
  where tblPropertyCategory.PCId=tblPropertySubCategory.PCId and
  tblPropertyCategory.PCId=@iPCId
  union
  select PCName
  from tblPropertyCategory INNER JOIN tblPropertySubCategory
  ON tblPropertyCategory.PCId=tblPropertySubCategory.PCId
  where tblPropertyCategory.PCId=tblPropertySubCategory.PCId and
  tblPropertyCategory.PCId=@iPCId

  <div>

  What I was doing earlier was that I was using PSCNAME in place of PSCName and PCNAME in place of PCName..

  Also no need to declare @iPCId int after union...

  Regards

  </div>
  Vishal Ranjan
  
  My Website
  www.dotnetvishal.com
  
  • Marked as answer by geniusvishal on Feb 25, 2012 07:46 AM