GoEddie / GoEddie/SSDT-DevPack

Suggestion: add substitution of SQLCMD variables to QuickDeploy

Open
#21 0 comments 1 reaction 0 assignees View on GitHub
Dominant language
C#
Stars
41
Forks
19
PR merge metrics
No merged PRs in 30d

Description

I was looking for a VS extension that does two things: 1) replaces CREATEs with ALTERs in individual artifact definitions (stored procedures, functions and views) in SSDT database projects and 2) does SQLCMD variable substitution. I found your SSDT-DevPack and it looks like QuickDeploy already does half of what I need. I particularly like Quick Deploy To Clipboard - very nice!

Would you be open to adding SQLCMD variable substitution to Quick Deploy? E.g., if there is a SQLCMD variable defined in the project and a stored procedure, function or a view references it, Quick Deploy would substitute it with a default value. Here is an example:

Let's say I have an SSDT database project with a database reference and the following stored procedure:

```
CREATE PROCEDURE [dbo].[spProcedure1]
AS
SELECT * FROM [$(SomeOtherDb)].dbo.Table1
```

If I run the current version of Quick Deploy To Clipboard on this SP, I get

```
if object_id('[dbo].[spProcedure1]') is null
begin
execute sp_executeSql N' create procedure [dbo].[spProcedure1] as select 1;';
end
GO
alter PROCEDURE [dbo].[spProcedure1]
AS
SELECT * FROM [$(SomeOtherDb)].dbo.Table1
GO
```

What I would like to get is
```
if object_id('[dbo].[spProcedure1]') is null
begin
execute sp_executeSql N' create procedure [dbo].[spProcedure1] as select 1;';
end
GO
alter PROCEDURE [dbo].[spProcedure1]
AS
SELECT * FROM SomeOtherDb.dbo.Table1
GO
```
(notice the difference in the FROM part of the SELECT statement)

The SQLCMD variable substitution should not be very complicated to implement as the default values for SQLCMD variables are stored in the database project:

![image](https://user-images.githubusercontent.com/178154/32669715-7fbe5ef4-c60f-11e7-95d6-166ff94e7a0b.png)

They are stored in the .sqlproj file in the following section:

```


SomeOtherDb
$(SqlCmdVar__2)


```

@GoEddie Do you have any interest in implementing this functionality? If not, or if you don't have the time, would you accept a PR implementing this functionality, if there were one? What requirements would you have for such a PR?

Thanks,
Sergei

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.