How do I inject variables into a SQL script, in the Azure DevOps pipeline?

user_181771 0
Viewed 924

Specifically, I need to know what is the syntax to populate the variables [variable_1] & [variable_2] in the script below:

INSERT [dbo].[ClientSecrets] ([Id], [Description], [Value], [Expiration], [Type], [Created], [ClientId]) 
VALUES (1, NULL, N'[variable_1]', NULL, N'SharedSecret', CAST(N'2020-06-24T03:38:48.1778227' AS DateTime2), 1)
GO
INSERT [dbo].[ClientSecrets] ([Id], [Description], [Value], [Expiration], [Type], [Created], [ClientId]) 
VALUES (2, NULL, N'[variable_2]', NULL, N'SharedSecret', CAST(N'2020-06-24T03:38:48.4931030' AS DateTime2), 2)
GO

Assume the pipeline has values for [variable_1] & [variable_2] and has been configured correctly. I just don't know what is expected of me on my end.

azure-devops sql-server
1 Answers

In the configuration file, you call somewhere your script file, such as :

- task: CmdLine@1
  displayName: Run Sqlcmd
  inputs:
    filename: Sqlcmd
    arguments: '-S $(ServerName) -U $(AdminUser) -P $(AdminPassword) -d $(DatabaseName) -i $(SQLFile)'

Use the command-line argument -v to pass some arguments, such as :

arguments: '-S $(ServerName) -U $(AdminUser) -P $(AdminPassword) -d $(DatabaseName) -i $(SQLFile) -v variable_1=$(something) variable_2=$(somethingElse)'

And then, in your SQL script :

INSERT [dbo].[ClientSecrets] ([Id], [Description], [Value], [Expiration], [Type], [Created], [ClientId])
-- Check this ---v-----------v
VALUES (1, NULL, $(variable_1), NULL, N'SharedSecret', CAST(N'2020-06-24T03:38:48.1778227' AS DateTime2), 1)
GO
INSERT [dbo].[ClientSecrets] ([Id], [Description], [Value], [Expiration], [Type], [Created], [ClientId])
-- Check this ---v-----------v
VALUES (2, NULL, $(variable_2), NULL, N'SharedSecret', CAST(N'2020-06-24T03:38:48.4931030' AS DateTime2), 2)
GO

Note that this is untested.

user_83985490
Related