假设我们有这样的结构:
CREATE TABLE [file]
(
mfile VARCHAR(15),
mopendt DATETIME,
mdesc1 VARCHAR(60),
mbillaty VARCHAR(8),
mrefer VARCHAR(60),
mclient INT,
mdept INT,
mprac INT
);
GO
INSERT INTO dbo.[file]
(
mfile,
mopendt,
mdesc1,
mbillaty,
mrefer,
mclient,
mdept,
mprac
)
VALUES
('mf1', GETDATE(), 'md1', 'mb1', 'staff', 1, 1, 1);
GO
CREATE TABLE [user]
(
tkfirst VARCHAR(50),
tklast VARCHAR(100),
tkinit VARCHAR(8),
tkeflag VARCHAR(5)
);
GO
INSERT INTO dbo.[user]
(
tkfirst,
tklast,
tkinit,
tkeflag
)
VALUES
('tkf1', 'thkl1', 'mb1', 'Y');
GO
CREATE TABLE client
(
clname VARCHAR(60),
clnum INT
);
GO
INSERT INTO dbo.client
(
clname,
clnum
)
VALUES
('cl1', 1);
GO
CREATE TABLE deptlab
(
head1 VARCHAR(40),
delcode INT
);
GO
INSERT INTO dbo.deptlab
(
head1,
delcode
)
VALUES
('h1', 1);
GO
CREATE TABLE praccode
(
pdesc VARCHAR(48),
pcode INT
);
INSERT INTO dbo.praccode
(
pdesc,
pcode
)
VALUES
('pd1', 1);
因此,以下代码应该有效:
DECLARE @HTMLSelect NVARCHAR(MAX),
@HTMLWhere NVARCHAR(MAX),
@timeperiod INT,
@deptcode VARCHAR(10),
@sql NVARCHAR(MAX);
SET @deptcode = NULL;
SET @timeperiod = '-14';
SET @HTMLSelect
= N'SELECT TOP 200
client.clname AS [Client],
[file].mfile AS [fileNo],
[file].mopendt AS [OpenDate],
[file].mdesc1 AS [file],
deptlab.head1 AS [Dept],
prac.pdesc AS [fileType],
[file].mbillaty AS [Code],
[user].tkfirst + '' '' + [user].tklast AS [Username],
[file].mrefer AS [ReferralType]
FROM [user] AS [user]
INNER JOIN [file] AS [file]
ON [user].tkinit = [file].mbillaty
INNER JOIN client AS client
ON client.clnum = [file].mclient
INNER JOIN deptlab AS deptlab
ON deptlab.delcode = [file].mdept
INNER JOIN praccode AS prac
ON prac.[pcode] = [file].mprac
';
SET @deptcode = NULL;
SET @HTMLWhere
= N'
WHERE
([user].tkeflag = ''Y'')
AND [file].mrefer like ''%staff%''
AND ([file].mopendt >= DATEADD(dd, ' + CAST(@timeperiod AS [NVARCHAR]) + N', GETDATE()))
';
IF OBJECT_ID('tempdb..#staffTable') IS NOT NULL
BEGIN
TRUNCATE TABLE #staffTable;
END;
ELSE
BEGIN
CREATE TABLE #staffTable
(
Client VARCHAR(60),
fileNo VARCHAR(15),
OpenDate DATETIME,
[file] VARCHAR(60),
Dept VARCHAR(40),
fileType VARCHAR(48),
FECode VARCHAR(8),
Username VARCHAR(150),
ReferralType VARCHAR(60)
);
END;
SET @sql = @HTMLSelect + @HTMLWhere;
PRINT @sql;
INSERT INTO #staffTable
EXEC sp_executesql @sql;
SELECT * FROM #staffTable