stored procedure logic - 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

General SQL Server Forums

New to SQL Server Programming

stored procedure logic

Author

Topic

abuhassan

105 Posts

Posted - 2006-09-28 : 07:34:28

Hi

i have written a script that collects the applications and the components each application uses.

I wanted to create a stored procedure so that i could get a snapshot of the application and the components that an application has at a given time.

i have the following database structure



CREATE TABLE [Components] (
	[CompID] [int] IDENTITY (1, 1) NOT NULL ,
	[RT] [nchar] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
	[Name] [nchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[Version] [smallint] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[CompID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [ApplicationID] (
	[ApplicationID] [int] IDENTITY (1, 1) NOT NULL ,
	[NodeID] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[WinVer] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
	[Hw] [nchar] (40) COLLATE Latin1_General_CI_AS NULL ,
	[BT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[MT] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[Cust] [nchar] (20) COLLATE Latin1_General_CI_AS NULL ,
	[BVer] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[BRel] [nchar] (10) COLLATE Latin1_General_CI_AS NULL ,
	[WStyle] [nchar] (15) COLLATE Latin1_General_CI_AS NULL ,
	[DateInst] [datetime] NULL ,
	[SnapDate] [datetime] NULL ,
	 PRIMARY KEY  CLUSTERED 
	(
		[ApplicationID]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [AppComponents] (
	[ApplicationID] [int] NOT NULL ,
	[CompID] [int] NOT NULL ,
	[InsT] [datetime] NULL ,
	[Installed] [bit] NULL ,
	[SnapDate] [datetime] NOT NULL ,
	CONSTRAINT [FK_AppsComponents_ApplicationID] FOREIGN KEY 
	(
		[ApplicationID]
	) REFERENCES [ApplicationID] (
		[ApplicationID]
	),
	CONSTRAINT [FK_AppsComponents_Components] FOREIGN KEY 
	(
		[CompID]
	) REFERENCES [Components] (
		[CompID]
	)
) ON [PRIMARY]
GO

is there a way of checking if the component is already in the data base if it is then just get the CompID and attach that CompId in the AppsComponents link table and put that in the AppComponents table. the snapdate field is to be the current datetime when the data is collected. Is there a current date function in SQL server? Is it possible to do this in a stored procedure?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-28 : 07:37:00

Add DEFAULT GETDATE() to the snapDate column to automatically enter current time to the record when inserting rows.

Peter Larsson
Helsingborg, Sweden

abuhassan

105 Posts

Posted - 2006-09-28 : 07:48:41

Is there a way of checking if the component is already in the data base if it is then just get the CompID and attach that CompId in the AppsComponents link table and put that in the AppComponents table. however if it does not existthen insert the data for a new component in the components table. the snapdate field is to be the current datetime when the data is collected which is to be within the procedure.Is it possible to do this in a stored procedure?

abuhassan

105 Posts

Posted - 2006-09-28 : 10:04:33

Can any one help?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-09-28 : 10:19:46

quote:
Originally posted by abuhassan

Can any one help?

There are people that patrol these boards like hawks constantly reading posts. I would suggest that you are not getting responses simply because your question doesn't make any sense.

Are you asking if it is possible to INSERT data if it doesn't exist and/or UPDATE data if it does? OK, I'll take a stab at answering that ... yeah, I think it may be possible, yup.

Are you asking for someone to write the queries for you? I won't

Are you have a particular problem with some query? By all means post it ... give us some sample data while you are at it.

By they way, care to explain the reasoning behind your table naming convention? I've never seen that before.

Jay White

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-09-28 : 10:29:45

Why don't you have a primary key on table AppComponents?

CODO ERGO SUM

abuhassan

105 Posts

Posted - 2006-09-28 : 11:53:29

thanks for the replies .... Let me explain my self

I have a program that collects information accross the intranet of what applications are installed on which

device(server or desktop). Each application uses/Consists of many different components. what the program

does it at any one time takes a snapshot of what components an application on a particular server is using.

This information previously was stored in a text file in a CSV format and then it was imported into the

database for each application (100's of CSV file per month).

The database has been designed several years ago and the table names were labelled then, I agree that the

naming convention is not good infact poor, i was going to alter the table names but ive been informed that

there are other applications that use the database, which are using the table names as they are.

I dont have a problem in creating the storedprocedures to insert the data in the database.

CREATE PROCEDURE insertApplicationID 

@ApplicationID Int, @NodeID nchar(15), @WinVer nchar(40), 
@Hw nchar(40), @BT nchar(20), @MT nchar(20), 
@Cust nchar(20), @BVer nchar(10),@BRel nchar(10), 
@WStyle nchar(15), @DateInst datetime
 AS 

INSERT INTO ApplicationID (applicationID, NodeID, WinVer, Hw, BT, 
	MT, Cust, BVer, BRel,
 	WStyle, DateInst, SnapDate)

VALUES (@ApplicationID, @NodeID, @WinVer, @Hw, @BT, 
	@MT, @Cust, @BVer,
 @BRel, @WStyle, @DateInst, GETDATE() )

go


/*

insertApplicationID 1, 'AppServer2', 'windows 2000 advanced server", 'IBM x series', '2MB Connection',
		'Server', 'Company Name', 'Ver2.3.0', 'Rel2.3.0', 'Intranet', null

*/



/*
###################################################################

*/


CREATE PROCEDURE insertComponents @CompID int, @RT nchar(7), 
@Name nchar(50), @Version smallint
 AS 

INSERT INTO Components (CompID, RT, [Name], Version)

VALUES ( @CompID, @RT, @Name, @Version)

go

/*
test data

insertComponents 4, 'TT1', 'Text File Parser', 2

*/



/*
###################################################################

*/

CREATE PROCEDURE insertAppsComponents @ApplicationID int, @CompID int, 
@InsT datetime, @Installed bit

 AS 

INSERT INTO AppsComponents (ApplicationID, CompID, 
InsT, Installed, SnapDate)

VALUES ( @ApplicationID, @CompID, 
@InsT, @Installed, GETDATE())

go

/*
test data

insertAppsComponents 4, 2, null, 1

*/

The problem that i have is that the when i want to insert any thing in the data base the program that

collects goes through the following steps....

1) Get application info including nodeID etc
2) check if node ID in applicationID table (Im using a select statemnet here)
3) If NodeID exists then get the applicationID and insert all the data using the insertApplicationID stored

procedure.
4) Get Component Info including name etc
5) check if name in Component table (Im using a select statemnet here)
6) If Name doesnt exists then insert all the data using the insertComponents stored procedure.
7) If NAme exists then get the CompID
8) insert all the data collected using the insertAppsComponents stored procedure. (this creates the link

between the application and the actual components)

What I meant (not to ask someone to write me a query but to ask):

Currently im doing alot of the logic in the program itself is it possible to do that type of steps 1-8 in a

stored procedure? Does any one have any ideas? would that be better for performance?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-09-28 : 12:20:36

A stored procedure is a stored batch of data modification language commands. If you can write each of you 1-8 steps as DML (SELECT, INSERT, UPDATE, DELETE) you can put those statements together inside a single store procedure.

Maybe my lack of solid reading comprehension skills are preventing me from understanding your question.

Jay White

abuhassan

105 Posts

Posted - 2006-09-28 : 12:36:39

Thanks....

By putting the steps 1-8 in a stored procedure rather than a program will that improve the performance of the application? Or is not recommended to do tht sort of processing in a stored procedure?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources