Working with Stored Procedures in Sqlserver-2005

Question

Post reply

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..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

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 · Answer 2

Aswanth

SSChasing Mays

Points: 608

March 19, 2008 at 2:55 am

#791320

Thank You..

It is Working..

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

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)