Can't find why this is incorrect - returns error

Question

0.00/5 (No votes)

See more:

I found this on the web under "TSQL - Solve it YOUR Way - Finding the percentage of NULL values for each column in a table" but when I run it I get the following error:

Msg 102, Level 15, State 1, Line 20
Incorrect syntax near ')'.

I can't find out why.

SQL

SET NOCOUNT ON
DECLARE @Statement NVARCHAR(MAX) = ''
DECLARE @Statement2 NVARCHAR(MAX) = ''
DECLARE @FinalStatement NVARCHAR(MAX) = ''
DECLARE @TABLE_SCHEMA SYSNAME = 'Production'
DECLARE @TABLE_NAME SYSNAME = 'Product'
SELECT
        @Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,
        @Statement2 = @Statement2 + COLUMN_NAME + '*100 / OverallCount AS ' + COLUMN_NAME + ',' + CHAR(13) FROM
INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA
IF @@ROWCOUNT = 0
        RAISERROR('TABLE OR VIEW with schema "%s" and name "%s" does not exists or you do not have appropriate permissions.',16,1, @TABLE_SCHEMA, @TABLE_NAME)
ELSE
BEGIN
        SELECT @FinalStatement =
                'SELECT ' + LEFT(@Statement2, LEN(@Statement2) -2) + ' FROM (SELECT ' + LEFT(@Statement, LEN(@Statement) -2) +
                ', COUNT(*) AS OverallCount FROM ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ') SubQuery'
        EXEC(@FinalStatement)

Posted 9-Oct-15 5:41am

dhMac

Add a Solution

Comments

PIEBALDconsult 9-Oct-15 11:47am

I think you're missing a closing apostrophe after the WHERE clause. And you don't show an END either.

dhMac 9-Oct-15 12:12pm

Where would the apostrophe go? ...and where would the END be put? To be honest I can't really find where the WHERE clause ends. I'm sorry for not knowing...this is a project due, it's exactly what I need but I can't seem to get it just right. I'm sure it works except for the error message. Smile | Smile | :)

John C Rayan 13-Oct-15 4:18am

Hi dhMac

You are missing an END for BEGIN. You could see my solution at the bottom. If you add END at the end you will be fine.

Please mark the solution as accepted if you are happy with the solution so as to close the thread.

Richard Deeming 9-Oct-15 13:23pm

Your query works fine for me on one of my tables. Do you have any strange column names? Try wrapping each COLUMN_NAME with a QUOTENAME(...):

@Statement = @Statement + 'SUM(CASE WHEN ' + QUOTENAME(COLUMN_NAME) + ' IS NULL THEN 1 ELSE 0 END) AS ' + QUOTENAME(COLUMN_NAME) + ',' + CHAR(13) ,
@Statement2 = @Statement2 + QUOTENAME(COLUMN_NAME) + ' * 100 / OverallCount AS ' + QUOTENAME(COLUMN_NAME) + ',' + CHAR(13)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

John C Rayan · Answer 1 · 2015-10-12T05:22:00

Solution 3

Hi dhMac

You are missing the END for BEGIN. See the last line.

SQL

SET NOCOUNT ON

DECLARE @Statement NVARCHAR(MAX) = ''

DECLARE @Statement2 NVARCHAR(MAX) = ''

DECLARE @FinalStatement NVARCHAR(MAX) = ''

DECLARE @TABLE_SCHEMA SYSNAME = 'Production'

DECLARE @TABLE_NAME SYSNAME = 'Product'

SELECT

        @Statement = @Statement + 'SUM(CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN 1 ELSE 0 END) AS ' + COLUMN_NAME + ',' + CHAR(13) ,

        @Statement2 = @Statement2 + COLUMN_NAME + '*100 / OverallCount AS ' + COLUMN_NAME + ',' + CHAR(13) FROM

INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TABLE_NAME AND TABLE_SCHEMA = @TABLE_SCHEMA

IF @@ROWCOUNT = 0

        RAISERROR('TABLE OR VIEW with schema "%s" and name "%s" does not exists or you do not have appropriate permissions.',16,1, @TABLE_SCHEMA, @TABLE_NAME)

ELSE

BEGIN

   SELECT @FinalStatement =

                'SELECT ' + LEFT(@Statement2, LEN(@Statement2) -2) + ' FROM (SELECT ' + LEFT(@Statement, LEN(@Statement) -2) +

                ', COUNT(*) AS OverallCount FROM ' + @TABLE_SCHEMA + '.' + @TABLE_NAME + ') SubQuery'

        EXEC(@FinalStatement)

END

Posted 12-Oct-15 5:22am

John C Rayan

Updated 13-Oct-15 1:57am

v2

Comments

Richard Deeming 12-Oct-15 14:12pm

Your "answer" just seems to be a repost of the code from the question.

What did you change?

John C Rayan 12-Oct-15 16:13pm

There is an END missing for BEGIN.

Richard Deeming 12-Oct-15 16:14pm

So how about adding that explanation to your answer, rather than expecting the user to play spot-the-difference with your code block?

John C Rayan 12-Oct-15 16:34pm

OP is expected to take the solution and he will definitely find out the extra line. It is not that difficult for a developer I guess

John C Rayan 13-Oct-15 1:57am

Can you tell me the reason for down voting the solution. It works..

Richard Deeming 13-Oct-15 7:38am

Reproducing the code from the question with one minor change, and not explaining what you changed, is not a good solution.

John C Rayan 13-Oct-15 7:49am

I have explained to the OP in the thread and whether the change is minor or major , as long as it fixed the problem , the solution is as good as any other solution. I do not understand what's your issue in the solution and I am surprised that you couldn't figure out missing END in the solution , it is very obvious.

Richard Deeming 13-Oct-15 7:54am

You added the explanation as a comment on the question 15 hours after I voted for your solution. The comment is not attached to your solution, and is not obvious to someone looking at your solution.

If you can't be bothered to add one line of explanation to your solution to accompany the code block, then it's not a good solution. The code may be correct, but without an explanation of what you changed, it's not a good solution.

In the time you've spent arguing about how good your solution is, you could have updated your solution with the missing explanation, told me that you'd done so, and I would have updated my vote.

Instead, you're wasting time arguing with me in the comments.

John C Rayan 13-Oct-15 7:58am

I have added the explanation to the OP in the solution.

Richard Deeming 13-Oct-15 8:00am

And I have updated my vote to 5.
(It might take a while to appear, depending on the caching.)

John C Rayan 13-Oct-15 8:06am

Thank you Richard!

dhMac 13-Oct-15 11:34am

Been out sick...haven't been able to respond.
Thanks to everyone who helped.