sqlproj: Add compiler error for mismatched number of select and insert columns
- 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
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