help with SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with SP

Author

Topic

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-10-10 : 11:50:19

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.serverid = Server_Name.Serverid 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 what result i got from my first SP to show the info.

I just want to combine both the SP into one. i don't have to loop in my code. 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

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-10 : 12:23:02

SELECT Users.FirstName, Server_Name.Server_Name, Server_Name.Serverid,
Users.approve, Users.UserID
into #a
FROM Users INNER JOIN
userserver ON Users.UserID = userserver.userid INNER JOIN
Server_Name ON userserver.id = Server_Name.id
WHERE (Users.Username = @username)

SELECT *
FROM schedule_info
WHERE _Date = @_Date
and serverid in (select Serverid from #a)
and approve=1
order by serverid

You could put the first query in place of the subquery using #a but I've donme this because it's clearer.
You would probably join to it to get other info frmo the query rather than use an "in" clause.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-10-10 : 12:44:12

i am kind of confused You could put the first query in place of the subquery using #a

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-10 : 12:50:53

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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

soorma
Yak Posting Veteran

52 Posts

Posted - 2007-10-10 : 17:00:27

i got my result but i have one more question if i want to show the server name and the first name and the last name hwo do i show it.
I was using this query before but the first name and last name were wrong.

SELECT U.FirstName, U.approve, U.UserID, SN.Server_Name, SN.Serverid, SI.Title, SI.Email, SI.Type, SI.PersonResponsible, SI.Starting_Time,
SI.Ending_Time, SI.AMPM, SI._Date
FROM Users U INNER JOIN
userserver US ON U.UserID = US.userid INNER JOIN
Server_Name SN ON US.serverid = SN.Serverid INNER JOIN
Schedule_Info SI ON SI.serverid = SN.Serverid AND SI._Date = '9/17/2007'
WHERE (U.Username = 'test')
ORDER BY US.serverid

i used ur query but i need to get the first name last name from the users table and the server name from the server_name table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources