October 10, 2007 at 9:58 am
I have 2 stored proc in my code. i want to combine the two SP into one.
In the ist stored proc i pass the username to get the roles that the user have. he can have multiple roles.
SELECT Users.FirstName, Server_Name.Server_Name, Server_Name.Serverid,
Users.approve, Users.UserID
FROM Users INNER JOIN
userserver ON Users.UserID = userserver.userid INNER JOIN
Server_Name ON userserver.id = Server_Name.id
WHERE (Users.Username = @username)
The first SP returns the rows like this
servername- test
serverid=1
userid=1
username=jon
servername = test2
serverid=2
userid=1
username=jon
servername = test3
serverid=3
userid=1
username=jon
Here is the second proc in that i pass the date and date is what the user selects from the calendar. the serverid is the value i get from the 1st stored proc. Then i loop in my code (passing the serverid)what result i got from my first SP to show the info.
I just want to combine both the SP into one. . SP gives the desired results.
SELECT * FROM schedule_info WHERE _Date = @_Date and serverid=@serverid and approve=1 order by serverid
Here are the tables
CREATE TABLE [Server_Name] (
[id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Server_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Serverid] [int] NOT NULL ,
CONSTRAINT [PK_Server_Name] PRIMARY KEY CLUSTERED
(
[Serverid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Schedule_Info] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[_Date] [datetime] NOT NULL ,
[Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonResponsible] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Starting_Time] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ending_Time] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AMPM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Approve] [bit] NULL ,
[userid] [int] NULL ,
[serverid] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [userserver] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[userid] [int] NULL ,
[serverid] [int] NULL ,
CONSTRAINT [PK_userserver] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[approve] [bit] NULL ,
[Passwordencrypt] [binary] (16) NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
October 10, 2007 at 5:02 pm
The thing you have no described is what you'd like to get. tell us what you'd like to pass in (as parameters), and what the resulting output should look like. Preferrably - just mock up a few records of what the NEW procedure should give you.
Doesn't look all that hard to combine - just don't know what kind of results you expect to get out of it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply