Return a value from a stored procedure

Question

Return a value from a stored procedure

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

TeraByteMe SSCarpal Tunnel Points: 4171 More actions · Answer 1

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 SSCarpal Tunnel Points: 4171 More actions · Answer 2

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 SSCarpal Tunnel Points: 4171 More actions · Answer 3

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 SSCarpal Tunnel Points: 4171 More actions · Answer 4

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))