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

Question

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.

You're calling the script file from Azure-Pipeline.yml or whatever you've renamed it ? — Cid
– Cid, Commented Jun 25, 2020 at 6:11

Cid · Accepted Answer · 2020-06-25 06:22:29Z

3

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.

answered Jun 25, 2020 at 6:22

Cid

15.3k4 gold badges33 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DaveDev Over a year ago

Hi Cid, I haven't accepted this yet because we're still trying to get some other stuff worked out before I can test it. I won't let it slide though.

Cid Over a year ago

@DaveDev no worries, tyt

Dan · Accepted Answer · 2024-11-11 21:21:00Z

0

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

edited Nov 11, 2024 at 21:21

answered Nov 11, 2024 at 21:20

Dan

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related