Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Return a value from a stored procedure Expand / Collapse
Author
Message
Posted Wednesday, April 4, 2012 10:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileDetail')
AND type IN (N'U') )
DROP TABLE dbo.FileDetail;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileMaster')
AND type IN (N'U') )
DROP TABLE dbo.FileMaster;
GO
CREATE TABLE dbo.FileMaster
(
FileID INT NOT NULL
IDENTITY(1, 1),
FilePathInitial NVARCHAR(260) NOT NULL,
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)
);
GO
CREATE TABLE dbo.FileDetail
(
FileDetailID INT NOT NULL
IDENTITY(1, 1),
FileID INT NOT NULL,
OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.
FilePathOriginal NVARCHAR(260) NOT NULL,
FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),
CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)
);
GO
DECLARE @FileID INT;
INSERT INTO dbo.FileMaster
(
FilePathInitial
)
VALUES (
N'\\server\drop\files\someFile.pgp'
);
SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'MOV',
N'\\server\drop\files\someFile.pgp',
N'\\server\drop\files\someFile_20120404_100322.pgp'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEC',
N'\\server\drop\files\someFile_20120404_100322.pgp',
N'\\server\drop\files\someFile_20120404_100322.txt'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEL',
N'\\server\drop\files\someFile_20120404_100322.txt',
NULL
);

-- show a file's lifespan
SELECT fd.FileDetailID,
fd.FileID,
fd.OperationType,
fd.FilePathOriginal,
fd.FilePathFinal,
fd.CreateDate
FROM dbo.FileMaster fm
JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID
WHERE fm.FileID = 1
ORDER BY fd.FileDetailID;
GO



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1278220
Posted Wednesday, April 4, 2012 10:25 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:09 PM
Points: 23,270, Visits: 31,985
TeraByteMe,

Looking at your original post, I think I know the problem. Modified the code to return max(id) + 1 and it doesn't use any dynamic sql.

ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
RETURN @MaxID
END

To test the code, run this:

declare @MyMaxID int;
exec dbo.sp_GetMaxID @MaxID = @MyMaxID OUTPUT;
select @MyMaxID;


As for what you are trying to explain to everyone, please let me know if I am paraphrasing this correctly.

The value of ID relates to one file, and there may be multiple instances of this ID in the table as the file moves around (what ever that means). Each time a new file is added, you need a new ID value, which you determine by getting the current MAX(ID) value and adding 1 to it.

Does that pretty much sum it up?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1278228
Posted Wednesday, April 4, 2012 10:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:09 PM
Points: 23,270, Visits: 31,985
Actually, I got this to work with no problem.

ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
END

Use the same test code from my post above.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1278236
Posted Wednesday, April 4, 2012 3:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562
Thanks Lynn for the code. Yes, I think you paraphrased it pretty well. The moving around of the file is the file and it's data being processed and moved to different directory. An analogy would be a TCP/IP packet along its journey across different points of a network. Each "hop" being a point where the file is processed and forwarded on. The ID of the file is captured at creation (when it comes into the FTP site). I want a identifying number for that initial file as I record each "hop" along its jouney. In short like this (many more data in columns will be capture):

ID FileName Directory Event
2001 Somefile_Renamed.txt \\server\dir Renamed
2001 Somefile.txt \\server\dir Deleted
2001 Somefile.txt \\server2\dir2 Created

I went ahead a created a Master type table that will capture the initial file creation on the FTP site. This table will created the ID via IDENTITY(1,1). I then will populate the table I depict above from that ID and record each event as it unfolds.




Lynn Pettis (4/4/2012)
Actually, I got this to work with no problem.

ALTER PROC dbo.sp_GetMaxID (@MaxID INT OUTPUT)
AS
BEGIN
SELECT @MaxID = max(ID) + 1 FROM tblFileWatchMaxID;
END

Use the same test code from my post above.







A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278450
Posted Wednesday, April 4, 2012 9:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562
opc.three (4/4/2012)
The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileDetail')
AND type IN (N'U') )
DROP TABLE dbo.FileDetail;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileMaster')
AND type IN (N'U') )
DROP TABLE dbo.FileMaster;
GO
CREATE TABLE dbo.FileMaster
(
FileID INT NOT NULL
IDENTITY(1, 1),
FilePathInitial NVARCHAR(260) NOT NULL,
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)
);
GO
CREATE TABLE dbo.FileDetail
(
FileDetailID INT NOT NULL
IDENTITY(1, 1),
FileID INT NOT NULL,
OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.
FilePathOriginal NVARCHAR(260) NOT NULL,
FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),
CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)
);
GO
DECLARE @FileID INT;
INSERT INTO dbo.FileMaster
(
FilePathInitial
)
VALUES (
N'\\server\drop\files\someFile.pgp'
);
SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'MOV',
N'\\server\drop\files\someFile.pgp',
N'\\server\drop\files\someFile_20120404_100322.pgp'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEC',
N'\\server\drop\files\someFile_20120404_100322.pgp',
N'\\server\drop\files\someFile_20120404_100322.txt'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEL',
N'\\server\drop\files\someFile_20120404_100322.txt',
NULL
);

-- show a file's lifespan
SELECT fd.FileDetailID,
fd.FileID,
fd.OperationType,
fd.FilePathOriginal,
fd.FilePathFinal,
fd.CreateDate
FROM dbo.FileMaster fm
JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID
WHERE fm.FileID = 1
ORDER BY fd.FileDetailID;
GO








A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278542
Posted Wednesday, April 4, 2012 9:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562


opc.three (4/4/2012)
The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileDetail')
AND type IN (N'U') )
DROP TABLE dbo.FileDetail;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileMaster')
AND type IN (N'U') )
DROP TABLE dbo.FileMaster;
GO
CREATE TABLE dbo.FileMaster
(
FileID INT NOT NULL
IDENTITY(1, 1),
FilePathInitial NVARCHAR(260) NOT NULL,
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)
);
GO
CREATE TABLE dbo.FileDetail
(
FileDetailID INT NOT NULL
IDENTITY(1, 1),
FileID INT NOT NULL,
OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.
FilePathOriginal NVARCHAR(260) NOT NULL,
FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),
CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)
);
GO
DECLARE @FileID INT;
INSERT INTO dbo.FileMaster
(
FilePathInitial
)
VALUES (
N'\\server\drop\files\someFile.pgp'
);
SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'MOV',
N'\\server\drop\files\someFile.pgp',
N'\\server\drop\files\someFile_20120404_100322.pgp'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEC',
N'\\server\drop\files\someFile_20120404_100322.pgp',
N'\\server\drop\files\someFile_20120404_100322.txt'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEL',
N'\\server\drop\files\someFile_20120404_100322.txt',
NULL
);

-- show a file's lifespan
SELECT fd.FileDetailID,
fd.FileID,
fd.OperationType,
fd.FilePathOriginal,
fd.FilePathFinal,
fd.CreateDate
FROM dbo.FileMaster fm
JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID
WHERE fm.FileID = 1
ORDER BY fd.FileDetailID;
GO








A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278544
Posted Wednesday, April 4, 2012 9:32 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562


opc.three (4/4/2012)
The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileDetail')
AND type IN (N'U') )
DROP TABLE dbo.FileDetail;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileMaster')
AND type IN (N'U') )
DROP TABLE dbo.FileMaster;
GO
CREATE TABLE dbo.FileMaster
(
FileID INT NOT NULL
IDENTITY(1, 1),
FilePathInitial NVARCHAR(260) NOT NULL,
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)
);
GO
CREATE TABLE dbo.FileDetail
(
FileDetailID INT NOT NULL
IDENTITY(1, 1),
FileID INT NOT NULL,
OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.
FilePathOriginal NVARCHAR(260) NOT NULL,
FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),
CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)
);
GO
DECLARE @FileID INT;
INSERT INTO dbo.FileMaster
(
FilePathInitial
)
VALUES (
N'\\server\drop\files\someFile.pgp'
);
SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'MOV',
N'\\server\drop\files\someFile.pgp',
N'\\server\drop\files\someFile_20120404_100322.pgp'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEC',
N'\\server\drop\files\someFile_20120404_100322.pgp',
N'\\server\drop\files\someFile_20120404_100322.txt'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEL',
N'\\server\drop\files\someFile_20120404_100322.txt',
NULL
);

-- show a file's lifespan
SELECT fd.FileDetailID,
fd.FileID,
fd.OperationType,
fd.FilePathOriginal,
fd.FilePathFinal,
fd.CreateDate
FROM dbo.FileMaster fm
JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID
WHERE fm.FileID = 1
ORDER BY fd.FileDetailID;
GO








A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278545
Posted Wednesday, April 4, 2012 9:33 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562


opc.three (4/4/2012)
The point Craig and I are trying to drive home is: unless you have a requirement that says that a sequence of IDs cannot have any gaps in it, then there is no need for you to maintain your own mechanism to generate new IDs.

From your write-up this is what I was thinking in terms of a basic data model. Some might call it a master-detail, or header-detail model. It is an example of a one-to-many parent-child relationship.

In the code below the master table that identifies a file uniquely is called FileMaster. Related to that table in the parent-child relationship is the FileDetail table, which maintains the list of operations a file goes through during it's lifetime. The relevant unique ID when tracking a file's movements is the FileMaster.FileID. FileDetail.FileDetailID is simply a way to uniquely arrive at a single row in the detail table, which would be useful to UI developers or for other query uses, like sorting, since it will be ever-increasing over time.

USE tempdb
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileDetail')
AND type IN (N'U') )
DROP TABLE dbo.FileDetail;
GO
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.FileMaster')
AND type IN (N'U') )
DROP TABLE dbo.FileMaster;
GO
CREATE TABLE dbo.FileMaster
(
FileID INT NOT NULL
IDENTITY(1, 1),
FilePathInitial NVARCHAR(260) NOT NULL,
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileMaster.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileMaster] PRIMARY KEY (FileID)
);
GO
CREATE TABLE dbo.FileDetail
(
FileDetailID INT NOT NULL
IDENTITY(1, 1),
FileID INT NOT NULL,
OperationType CHAR(3) NOT NULL, -- CPY=copy, DEL=delete, MOV=Move/Rename, DEC=decrypt etc.
FilePathOriginal NVARCHAR(260) NOT NULL,
FilePathFinal NVARCHAR(260) NULL, -- will be null when OperationType = D
CreateDate DATETIME NOT NULL
CONSTRAINT [df_dbo.FileDetail.CreateDate] DEFAULT (GETDATE()),
CONSTRAINT [pk_dbo.FileDetail] PRIMARY KEY (FileDetailID),
CONSTRAINT [fk_dbo.FileDetail.FileID_dbo.FileMaster.FileID] FOREIGN KEY (FileID) REFERENCES dbo.FileMaster (FileID)
);
GO
DECLARE @FileID INT;
INSERT INTO dbo.FileMaster
(
FilePathInitial
)
VALUES (
N'\\server\drop\files\someFile.pgp'
);
SET @FileID = SCOPE_IDENTITY();

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'MOV',
N'\\server\drop\files\someFile.pgp',
N'\\server\drop\files\someFile_20120404_100322.pgp'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEC',
N'\\server\drop\files\someFile_20120404_100322.pgp',
N'\\server\drop\files\someFile_20120404_100322.txt'
);

INSERT INTO dbo.FileDetail
(
FileID,
OperationType,
FilePathOriginal,
FilePathFinal
)
VALUES (
@FileID,
'DEL',
N'\\server\drop\files\someFile_20120404_100322.txt',
NULL
);

-- show a file's lifespan
SELECT fd.FileDetailID,
fd.FileID,
fd.OperationType,
fd.FilePathOriginal,
fd.FilePathFinal,
fd.CreateDate
FROM dbo.FileMaster fm
JOIN dbo.FileDetail fd ON fm.FileID = fd.FileID
WHERE fm.FileID = 1
ORDER BY fd.FileDetailID;
GO








A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278546
Posted Wednesday, April 4, 2012 9:33 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 11:33 AM
Points: 163, Visits: 562
Opc.three and Craig thank you very much for you advice and efforts in sharing your knowledge. I do very much value your replies. Thanks.







A clever person solves a problem. A wise person avoids it.
A clever person solves a problem. A wise person avoids it.



A clever person solves a problem. A wise person avoids it. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Post #1278547
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse