Jordan Zapf Jordan Zapf - 1 month ago 11
SQL Question

EXEC an INSERT with a dynamic Schema TSQL SSMS

I know that's a little bit much, but I'm searching for a solution since 4 days. I just want to EXEC a INSERT, but I always get some cryptic Error Messages in SSMS

INPUT(variables are wrong so the ErrorLog should get filled):

USE [Tfs_GitInterface]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spInsertChangeLevel2]
@repositoryName = N'asdasd',
@commitId = 213123,
@changeLevel = N'asda'
SELECT 'Return Value' = @return_value
GO


Stored Procedure:

CREATE PROCEDURE [dbo].[spInsertChangeLevel2]
-- Add the parameters for the stored procedure here
@repositoryName [nvarchar](256),
@commitId [binary](20),
@changeLevel [nvarchar](400)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @repId [int]
DECLARE @commitIdValid [int];
DECLARE @errorOccured [BIT] = 0;
DECLARE @schema [nvarchar](400) = 'dbo';
DECLARE @schemaPlusTable [nvarchar](400) = @schema + '.tbl_ErrorLog';
DECLARE @changeErrorMsg [nvarchar](400) = 'Wrong ChangeLevel';
DECLARE @changeSQL [nvarchar](MAX);

BEGIN TRY
IF
(@changeLevel <> 'NewFeature' AND @changeLevel <> 'Patch' AND @changeLevel <>'BreakingChange')
BEGIN
PRINT 'Wrong ChangeLevel';

EXEC('INSERT INTO [' + @schema + '].[tbl_ErrorLog] ( [repositoryName], [commitId], [changeLevel], [error], [timestamp] )
VALUES ( "test" , ' + @commitId + ' , ' + @changeLevel + ' , ' + @changeErrorMsg + ' , SYSDATETIME() )');

SET @errorOccured = 1;
END

IF NOT EXISTS
(SELECT InternalRepositoryId FROM dbo.tbl_GitRepository WHERE Name = @repositoryName)
BEGIN
PRINT 'Repository-Name ist nicht bekannt'
INSERT INTO [dbo].[tbl_ErrorLog]
([repositoryName]
,[commitId]
,[changeLevel]
,[error]
,[timestamp])
VALUES
(@repositoryName
,@commitId
,@changeLevel
,'Repository-Name is unknown'
,SYSDATETIME())
SET @errorOccured = 1;
END

SELECT @repId = InternalRepositoryId FROM
dbo.tbl_GitRepository WHERE
Name = @repositoryName;

IF NOT EXISTS
(SELECT CommitId FROM dbo.tbl_GitCommit WHERE CommitId = @commitId AND InternalRepositoryId = @repId)
BEGIN
PRINT 'CommitId is unknown'
INSERT INTO [dbo].[tbl_ErrorLog]
([repositoryName]
,[commitId]
,[changeLevel]
,[error]
,[timestamp])
VALUES
(@repositoryName
,@commitId
,@changeLevel
,'CommitId is unknown'
,SYSDATETIME())
SET @errorOccured = 1;
END

IF(@errorOccured = 1) RETURN 0;

INSERT INTO [dbo].[tbl_ChangeLevel]
([InternalRepositoryId]
,[CommitId]
,[ChangeLevel])
VALUES
(@repId
,@commitId
,@changeLevel)
END TRY
BEGIN CATCH
PRINT 'Msg: ' + ERROR_MESSAGE();
PRINT ERROR_LINE();
PRINT ERROR_NUMBER();
PRINT 'Procedure: ' + ERROR_PROCEDURE();
PRINT ERROR_STATE();
RETURN 0;
END CATCH
RETURN 1;
END


It's actually pretty simple stuff, this stored procedure tests if the 3 variables are legit/known and if so, its inserts them into the tbl_ChangeLevel-table. For every wrong variable its creates a row in the tbl_ErrorLog-table, that means either we have one new row in tbl_ChangeLevel or 1-3 in tbl_ErrorLog.

At the moment I changed the first test for an error at ChangeLevel to a dynamic EXEC so the schema(@schema) can be changed. I tried literally everything here. At first I used sp_executesql and EXECUTE, but nothing worked and now when I debug this, it catches an error:

Wrong ChangeLevel
Msg: Incorrect syntax near '̀'.

2

102

1

(1 row(s) affected)

And yes thats not dirt on your screen -> '̀' <- its one of these things à, but its no where in my code, I already searched row for row.... I had already other Errors like an Enter-Symbol, " " or "." I'm frustrated :/ Pls help

Answer

You must have copy paste part of your query from outlook or other source. Other sources have different encoding format. If you have text editor like Notepad++ or ultraedit, change your query encoding to UTF-8 without BOM.

If the problem still persists, I would recommend to rewrite the query in SSMS query window.

Comments