CODEWITHSUNDEEP
sql-serverazure-devops
DaveDev
DaveDev

Reputation: 42185

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

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.

Upvotes: 3

Views: 2647

Answers (2)

Dan
Dan

Reputation: 1

Kind of late to the party here but I needed to do this today:

ADO pipeline makes it almost too easy. Treat this just like a powershell or AZcli script. Create a pipeline var & call it with $(var)

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = '$(client)_user')
    BEGIN
    Print '$(client)_user already exists.  Dropping to reinitialize'
    DROP USER [$(client)_user]
    END

Upvotes: 0

Cid
Cid

Reputation: 15247

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.

Upvotes: 3

Related Questions