Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Return a value from a stored procedure


Return a value from a stored procedure

Author
Message
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8249 Visits: 14368
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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?

Cool
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)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24227 Visits: 37978
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.

Cool
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)
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
TeraByteMe
TeraByteMe
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 589
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. ~ Einstein
select cast (0x5365616E204465596F756E67 as varchar(128))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search