Problem with syntax in Dynamic Sql statement

Austin Muts

7y857SQL

I am struggling with syntax when writing this dynamic SQL.Can someone help

DECLARE @SQL NVARCHAR(MAX) = '';

BEGIN

SET @SQL = 'INSERT [dbo].[SysDocField] ([ID], [DocID], [FieldNo], [FieldName], [DisplayName], [Description], [DefaultValue], [DisplayFormat], [MaxLength], [MinLength], [DataType], [ColumnName], [BarcodeNo], [DataBehaviour], [ListID], [MapToString], [ScanIndex], [ScanRequired], [ValidationID], [RegexValue], [ValidationTrigger], [DecimalPrecision], [DBCanNull], [IsPrimary], [IsAuditable], [DBSchemaDefault], [DBSchemaDefaultValue], [Updated], [UpdatedBy], [Created], [CreatedBy], [SourceID])'

'VALUES ('341faf49-2749-434a-b8ba-96f8c5abbd8e', 'b5f3cf6e-190b-4132-b90e-d320414cfaee', 1, 'ApplicationID', 'Application_ID', N'Idx1', N'', N'', 30, 0, 0, N'ApplicationID', 0, 0, N'', 0, 0, 0, N'', NULL, 4, 0, 1, 0, 0, 0, NULL, CAST(N'2019-09-23 10:26:35.943' AS DateTime), N'1aa591f0-a2c1-41b3-815b-68e0f5ef9478', CAST('2019-09-23 10:19:25.197' AS DateTime), '1aa591f0-a2c1-41b3-815b-68e0f5ef9478', NULL)'

EXECUTE sp_executesql @SQL

END

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya7y ago
Accepted answer
Hello Austin,

Whenever you have confuse dynamic query written, you have used below scenarion in your query.

first you have to write the normal query for insert and replace with single quote(') to double single quote('') not used directly double quote.

can you try this one I have updated your query

DECLARE @SQL NVARCHAR(MAX) = '';
BEGIN
SET @SQL = 'INSERT [dbo].[SysDocField] ([ID], [DocID], [FieldNo], [FieldName], [DisplayName], [Description], [DefaultValue], [DisplayFormat], [MaxLength], [MinLength], [DataType], [ColumnName], [BarcodeNo], [DataBehaviour], [ListID], [MapToString], [ScanIndex], [ScanRequired], [ValidationID], [RegexValue], [ValidationTrigger], [DecimalPrecision], [DBCanNull], [IsPrimary], [IsAuditable], [DBSchemaDefault], [DBSchemaDefaultValue], [Updated], [UpdatedBy], [Created], [CreatedBy], [SourceID])
VALUES (''341faf49-2749-434a-b8ba-96f8c5abbd8e'', ''b5f3cf6e-190b-4132-b90e-d320414cfaee'', 1, ''ApplicationID'', ''Application_ID'', N''Idx1'', N'''', N'''', 30, 0, 0, N''ApplicationID'', 0, 0, N'''', 0, 0, 0, N'''', NULL, 4, 0, 1, 0, 0, 0, NULL, CAST(N''2019-09-23 10:26:35.943'' AS DateTime), N''1aa591f0-a2c1-41b3-815b-68e0f5ef9478'', CAST(''2019-09-23 10:19:25.197'' AS DateTime), ''1aa591f0-a2c1-41b3-815b-68e0f5ef9478'', NULL)'
EXECUTE sp_executesql @SQL
END

let me know if you have needed any help from my side.

Thanks
Neha Trivedi7y ago
Hello

you can use EXECUTE (@SQL) instead of EXECUTE sp_executesql @SQL

The below link will help you for more understanding
https://stackoverflow.com/questions/5196434/dynamic-insert-into-variable-table-statement-sql-server