0

Firstly, may I state that I'm aware of the ability to, e.g., create a new function, declare variables for rowcount1 and rowcount2, run a stored procedure that returns a subset of rows from a table, then determine the entire rowcount for that same table, assign it to the second variable and then 1 / 2 x 100....

However, is there a cleaner way to do this which doesn't result in numerous running of things like this stored procedure? Something like 

select (count(*stored procedure name*) / select count(*) from table) x 100) as Percentage...

Sorry for the crap scenario!

EDIT: Someone has asked for more details. Ultimately, and to cut a very long story short, I wish to know what people would consider the quickest and most processor-concise method there would be to show the percentage of rows that are returned in the stored procedure, from ALL rows available in that table. Does that make more sense?

The code in the stored procedure is below:

SET @SQL = 'SELECT COUNT (DISTINCT c.ElementLabel), r.FirstName, r.LastName, c.LastReview, 
    CASE
        WHEN c.LastReview < DateAdd(month, -1, GetDate())  THEN ''OUT of Date''
        WHEN c.LastReview >= DateAdd(month, -1, GetDate()) THEN ''In Date'' 
        WHEN c.LastReview is NULL THEN ''Not Yet Reviewed'' END as [Update Status]

    FROM [Residents-'+@home_name+'] r
    LEFT JOIN [CarePlans-'+@home_name+'] c ON r.PersonID = c.PersonID
    WHERE r.Location = '''+@home_name+'''
    AND CarePlanType = 0
    GROUP BY r.LastName, r.FirstName, c.LastReview
    HAVING COUNT(ELEMENTLABEL) >= 14

Thanks Ant

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ant
  • 73
  • 9
  • 1
    You are assuming that there is *that table*, which in general case there isn't. If your stored procedure is so simple that all it does is selecting some rows from a single table, without even joins, then you already have your answer in the form of `select (@@rowcount / select count(*) from table) x 100 as Percentage`, which you can put inside that same procedure. – GSerg Dec 30 '19 at 20:40
  • 1
    Beware SQL injection! Building a dynamic SQL query like this can be very dangerous. https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work – Paul Williams Dec 30 '19 at 20:40
  • Before anything you **need** to fix that Dynamic SQL. But why is `@home_name` both a dynamic object **and** a parameter? This smells strongly like a huge design flaw here, and that `home_name` should be a column in a table, not the suffix for your objects. If the object is called [Residents-Smith]`, why have a column with the value `'Smith'` in the table when it's in the name? The design doesn't make sense. – Thom A Dec 30 '19 at 20:42
  • Thanks. I wasn't asking for general comments about the design. In actual fact the residents-home_name is because the SP would be called for different homes, and I've set up different residents- tables for each of those homes. It is therefore different depending on which home I'm looking for results for. Noted re the injection - it's something I'm going to sort out once all the features are implemented - and thank you! @GSerg, OK thanks very much, if that's the best way then that's fair enough. – Ant Dec 30 '19 at 20:50
  • But why have a different table for each house at all? That's poor design and *why* you've written SQL that is open to injection. Fix the design, get rid of the huge security flaw. – Thom A Dec 30 '19 at 21:11
  • I have a different table for each home because I want it to. I don't wish to change it, and I wasn't asking for help on that matter. There are many people who give their time and knowledge to help people on SO, and I've been full of gratitude when they offer me help. I must say there are many people here who just seem to offer less than constructive help and 'advice'. I'm not a professional DBA, and am doing this as I go along. I get it, you know your subject matter, and you know it more than me. But perhaps remember that once you were learning, and this is not my profession as it is yours. – Ant Dec 31 '19 at 11:59

1 Answers1

0

I could not tell from your question if you are attempting to get the count and the result set in one query. If it is ok to execute the SP and separately calculate a table count then you could store the results of the stored procedure into a temp table.

CREATE TABLE #Results(ID INT, Value INT)

INSERT #Results EXEC myStoreProc @Parameter1, @Parameter2

SELECT 
     Result = ((SELECT COUNT(*) FROM #Results) / (select count(*) from table))* 100
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • Hi Ross. Yes that's it. I think I was kind of asking a question I already knew the answer to, which is why it was a bit difficult to articulate it properly. But yes, I do want basically a query that tells me the row count for the SP, and then the number of rows in that table as a whole, to then work out the percentage. Thank you very much for your help :) – Ant Dec 31 '19 at 12:00