Store procedure help - searching multiple tables

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)

Store procedure help - searching multiple tables

Author

Topic

KTL_DK
Starting Member

15 Posts

Posted - 2003-11-04 : 07:46:06

hello ,

I have a database with one main table : servers, this table
have 3 tables associated : software_installed,patchs_aplied,services_running
The relations are one to many (from servers to the 3 tables)
There are 3 more tables in the database: software(all software that could be installed),patchs(all patches that could be aplied),services(all possible services that could run in the servers) . Each of this tables are associated with the first 3 tables .

My question is related with the construction of a store procedure. This procedure should accept 4 parameters : serverid,softwareid,patchid,serviceid
and perform a search with the values passed .

For instace if i call the procedure : exec serverproc -1,1,2,3 the procedure should return all servers that match these conditions :

All Servers that have the software: windows2k(softwareid=1) installed ,
and patch: ms651(patchid=2) aplied, and the service FTP(serviceid=3) running

I'm having some dificulties in this store procedure, so could someone tell me how could i build a procedure with the desired functionalities ?

Thanks in advance

tables :
------------------------------------------------

SERVERS
serverid
servername
serverdesc
------------------------------------------------
PATCHES_APLIED
serverid (fk servers.serverid)
softwareid (fk patches.patchid)
dateinstaled

SERVICES_RUNNING
serverid (fk servers.serverid)
patchid (fk services.serviceid)
dateaplied

SOFTWARE_INSTALLED
serverid (fk servers.serverid)
softwareid (fk software.softwareid)
runningonport
----------------------------------------------
SOFTWARE
softwareid
SoftwareName

PATCHES
patchid
patchname

SERVICES
ServiceID
ServiceName
--------------------------------------

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-04 : 08:51:18

Read and follow links. This will help get you to a solution faster.

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=29090[/url]

KTL_DK
Starting Member

15 Posts

Posted - 2003-11-04 : 10:21:13

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-11-04 : 10:46:11

You can do searches like this:


SELECT [s].[ColA], [s].[ColB]
FROM [dbo].[Servers] AS [s]
INNER JOIN [dbo].[SearchTableA] ...
WHERE <<Search Terms>>

UNION ALL

SELECT [s].[ColA], [s].[ColB]
FROM [dbo].[Servers] AS [s]
INNER JOIN [dbo].[SearchTableB] ...
WHERE <<Search Terms>>

UNION ALL

SELECT [s].[ColA], [s].[ColB]
FROM [dbo].[Servers] AS [s]
INNER JOIN [dbo].[SearchTableC] ...
WHERE <<Search Terms>>

UNION ALL

...

Does this help get you started?

~ monkey

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-11-04 : 11:29:13

I had noticed you had not received much response and the reason is you need to do more work. Help on this forum is on people's own time and if you would provide CREATE TABLE, INSERT statements with sample data and desired results you would probably find help much more readily. As your question is written, someone here would have to guess at your table structure and sample data, write code for the table creation and inserts, and provide a solution. That was what the links showed.

KTL_DK
Starting Member

15 Posts

Posted - 2003-11-04 : 12:26:42

Ok i've included the tables

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-04 : 12:32:57

KTL_DK, you need to provide CREATE TABLE statements and not just list the column names. You also need to provide INSERT INTO statements for sample data as well as expected result set using the sample data. The link provided illustrates what information needs to be given to us in order for us to assist you.

Tara

DavidD
Yak Posting Veteran

73 Posts

Posted - 2003-11-04 : 18:47:42

What you asked for does'nt make sense in that all you need to return server information would be serverid.
It would make more sense if you did not pass ServerID into the proc
ie.

create proc prc_GetServers @softwareid int, @patchid int, @serviceid int
as
select se.*
from server se
join patches_aplied pa on se.serverid = pa.serverid
join services_running sr on se.serverid = sr.serverid
join software_installed si on se.serverid = si.serverid
where pa.softwareid = @softwareid
and sr.patchid = @patchid
and si.softwareid = @serviceid --??????

Anyway use this as the basis to get what you want. Hope it helps
Regards
David

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources