Working with Stored Procedures in Sqlserver-2005

• Aswanth

  SSChasing Mays

  Points: 608

  More actions

  March 18, 2008 at 11:24 pm

  #187536

  I'm Using ASP.Net with C# & SqlServer-2005..

  In my Sqlserver, I'm having two Databases..

  ie User's_Details & Company_Details

  I'm having one Stored Procedure(Get_All_Details) in which I'm Joining both the Databases.. ie I'm getting the Output from both the Databases... In this Stored Procedure i'm not having any Input Parameters..

  -------------------------------------------------------------------------------------------------------------------------------

  Create procedure Get_All_Details

  as

  begin

  select

  Users.FirstName, Users.LastName, Users.UserName, Category.Category_Name, Category.Start_Date, Category.End_Date,

  Employees.Company_ID, Employees.Name, Employees.Salary

  from

  Users, Category, Employees,

  Company_Details.dbo.Main_Categories, Company_Details.dbo.Departments

  where

  Category.Category_ID=Employees.Category_ID

  and

  Category.Users_Category_ID=Users.User_ID

  and

  Employees.Main_Category_ID=Employees.Category_ID

  and

  Company_Details.dbo.Main_Categories.Main_Category_ID=Employees.Category_ID

  and

  Category.Category_ID='10'

  end

  -------------------------------------------------------------------------------------------------------------------------------

  In this Stored Procedure i'm getting all details.. Here i'm not having any Input Parameters..

  I'm getting Details Particular Category ( Category.Category_ID='10' )..

  But Now I want the Input Parameter. So that I can get all Users Details Dynamically Here ..

  how to put the input Parameter..

  i tried it like this..

  ---------------------------------------------------------------------------------------------------------------------

  Create procedure Get_All_Details

  @Category_ID int

  as

  begin

  --------------

  -----------

  --------

  end

  ---------------------------------------------------------------------------------------------------------------------

  But i'm not getting That Particular Categories Details.. i'm getting all Categories Values even i entered One Category_ID(ie 10)..

  How do I get Values of Specified Category_ID dynamically..

  Please give me the Suggestions ..

  Thank you..

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  March 19, 2008 at 12:15 am

  #791292

  Try this:

  Create procedure Get_All_Details( @Category_ID int )

  as

  begin

  select

  Users.FirstName, Users.LastName, Users.UserName, Category.Category_Name, Category.Start_Date, Category.End_Date,

  Employees.Company_ID, Employees.Name, Employees.Salary

  from

  Users, Category, Employees,

  Company_Details.dbo.Main_Categories, Company_Details.dbo.Departments

  where

  Category.Category_ID=Employees.Category_ID

  and

  Category.Users_Category_ID=Users.User_ID

  and

  Employees.Main_Category_ID=Employees.Category_ID

  and

  Company_Details.dbo.Main_Categories.Main_Category_ID=Employees.Category_ID

  and

  Category.Category_ID=@Category_ID

  end

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• Aswanth

  SSChasing Mays

  Points: 608

  More actions

  March 19, 2008 at 2:55 am

  #791320

  Thank You..

  It is Working..

• Jeff Moden

  SSC Guru

  Points: 1004589

  More actions

  March 19, 2008 at 6:32 am

  #791402

  I also recommend you lookup CREATE PROCEDURE in Books Online and study it... there's a wealth of information about parameters and defaults for parameters there.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)