下面是返回行号的解决方案。我们正处在一个需要参数的大过程中。本质上,作为tSQL开发人员,您需要猜测问题将发生在哪里,通常是围绕正式参数输入的参数。
-- Preamble
CREATE PROCEDURE [Meta].[ValidateTable]
@DatabaseNameInput VARCHAR(100), -- = 'DatabaseNameInput',
@TableNameInput VARCHAR(100), -- = 'TableNameInput',
@SchemaNameInput VARCHAR(100), -- = 'SchemaNameInput',
AS
BEGIN
DECLARE @crlf CHAR(2) = CHAR(13) + CHAR(10),
-----------Database Validity------------------
@IsDatabaseValid BIT,
@DatabaseNumber INTEGER,
@DatabaseNamePredicate NVARCHAR(100),
@CurrentExecutingContext NVARCHAR(40),
@DatabaseValidityExecutable NVARCHAR(100),
@DatabaseParameterString NVARCHAR(50),
-----------Table Validity------------------
@TableObjectIdentity INTEGER,
@TableString NVARCHAR(500),
@TableParameterString NVARCHAR(50),
@TableValidityExecutable NVARCHAR(200),
-----------Error Handling------------------
@ErrorState INTEGER = 0,
@ErrorNumber INTEGER = 0,
@ErrorSeverity INTEGER = 0,
@MyErrorMessage NVARCHAR(150),
@SetMessageText NVARCHAR(1024) = 'No Error Message Text for sys.messages.',
@ErrorDescription NVARCHAR(1024) = 'No error description was given.';
-- Be aware of SQL Injection Risk with no semi-colons at the line tails
SET @TableString = 'N' + '''' + @DatabaseNameInput + '.' + @SchemaNameInput + '.' + @TableNameInput + '''';
SET @DatabaseParameterString = N'@DatabaseNumber INTEGER OUTPUT ';
SET @TableParameterString = N'@TableObjectIdentity INTEGER OUTPUT';
-- Phase 0.0, testing for database existence.
PRINT 'Table Validity Executable: ' + @TableValidityExecutable;
EXECUTE sp_executesql @DatabaseValidityExecutable, @DatabaseParameterString, @DatabaseNumber = @DatabaseNumber OUTPUT;
IF @DatabaseNumber IS NULL
BEGIN
SET @MyErrorMessage = 'The @DatabaseNameInput parameter: "%s" specified by the caller does not exist on this SQL Server - ' + @@SERVERNAME;
EXECUTE sys.sp_addmessage @msgnum = 59802, @severity = 16, @msgtext = @MyErrorMessage, @replace = 'replace', @lang = 'us_english';
RAISERROR(59802, 15, 1, @DatabaseNamePredicate);
END;
-- Phase 0.1, testing for table existence.
PRINT 'Table Validity Executable: ' + @TableValidityExecutable;
EXECUTE sp_executesql @TableValidityExecutable, @TableParameterString, @TableObjectIdentity = @TableObjectIdentity OUTPUT;
IF @TableObjectIdentity IS NULL
BEGIN
SET @MyErrorMessage = 'The @TableNameInput parameter: "%s" specified by the caller does not exist in this database - ' + DB_NAME() +';';
EXECUTE sys.sp_addmessage @msgnum = 59803, @severity = 16, @msgtext = @MyErrorMessage, @replace = 'replace', @lang = 'us_english';
RAISERROR(59803, 15, 1, @TableString);
END;