microsoft / microsoft/DacFx

sqlproj: Add compiler error for mismatched number of select and insert columns

Open
#368 0 comments 1 reaction 0 assignees View on GitHub
enhancement
Dominant language
C#
Stars
460
Forks
29
Avg merge
4d 9h
Merged PRs (30d)
7

Description

**Is your feature request related to a problem? Please describe.**
Create an empty sqlproj project. Add the following objects:
```sql
CREATE TABLE TestTable(Col1 VARCHAR(250))
GO
CREATE PROCEDURE [dbo].[BadProc]
AS
INSERT INTO
TestTable
(Col1)
SELECT 'a', 'b'
```
Try to deploy this project to a database and you get the error:
```diff
- The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.
```
**Describe the solution you'd like**
I would like msbuild to catch these insert/select column number mismatch errors at compile time and throw a compilation error. These types of errors are very common when using ssdt projects inside of source control. If somebody misses a line while resolving a merge conflict, they won't know it will cause an error until they try to deploy it. Catching these errors early would catch a whole class of deployment errors.

There might be cases where a column number mismatch won't result in a deployment error but I'm not aware of any. Regardless, I think msbuild should be validating this basic case with simple tables and columns.

**Alternative solutions**
If it's not safe to always throw a compiler error, a tsql warning would be good at least.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the sqlproj example with TestTable and BadProc, then compare the current msbuild compilation behavior with the deployment error. Determine where SQL project compilation validates INSERT and SELECT column counts; done means the mismatch is reported during compilation, with the warning alternative resolved.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
build-system, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.