Jordan Zapf Jordan Zapf - 1 year ago 61
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 Source

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.