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 10:38 am
Are you the output of the first sp anywhere else? If not you may just want to use this query
SELECT * FROM schedule_info WHERE _Date = @_Date and serverid IN ( SELECT server_name.serverid FROM Users INNER JOIN
userserver ON Users.UserID = userserver.userid INNER JOIN
Server_Name ON userserver.id = Server_Name.id
WHERE (Users.Username = @username) and approve=1 ) order by serverid
Prasad Bhogadi
www.inforaise.com
October 10, 2007 at 11:07 am
it is not giving the right results. IN my frist proc i pass the username to get what the user have access to what servers. He can have access to multiple servers. the frist proc will give multiple rows with different serverid.
example
servername = test1
serverid=1
userid=1
username=jon
servername = test2
serverid=2
userid=1
username=jon
servername = test3
serverid=3
userid=1
username=jon
I grab the serverid one by one and pass to another SP to get the result. I hope i make sense now.
here is teh second proc sql
SELECT * FROM schedule_info WHERE _Date = @_Date and serverid=@serverid and approve=1 order by serverid
October 11, 2007 at 12:55 am
If you furnish a bit of test data and the desired results that you are expecting by merging both stored procedures, it should be hard to combine your procedures into one. However please provide relevant requirements on what you are expecting.
Prasad Bhogadi
www.inforaise.com
October 11, 2007 at 11:57 am
i think i figured it out. thanks for ur help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply