Update: I Solved this. Obviously I reinvented the wheel, but I did not immediately find the answer where I searched.
Given there is another question exactly the same as mine, but that does not answer my question, I will try to be very clear. My question is not answered because the answers do not indicate how to accomplish my task. I don't really care how I accomplish it, but there has to be a way.
I want the ability to count occurrences by level of any two discrete columns in an arbitrary table. I want to store the results for later reference because the query takes a long time to run.
The table name and two column names should be definable.
Based on a lot of research, it appears that a function, not a procedure should be used, but I am more interested in what happens, not how it happens.
DROP FUNCTION IF EXISTS O_E_1
GO
DROP TABLE IF EXISTS TestTable
GO
CREATE FUNCTION O_E_1
(@feature NVARCHAR(128), @table NVARCHAR(128))
RETURNS TABLE
AS
RETURN
(SELECT
COUNT(DISTINCT [PersonID]) AS count_person,
@feature AS feature, [HasT2DM] AS target
FROM
dbo.@table
GROUP BY
[@feature], [HasT2DM]);
GO
SELECT *
INTO TestTable
FROM O_E_1('Diagnosis', 'PatientDiagnoses')
go
I hope that with a little bit of work, I can accomplish this.
I have a version that does this in a procedure using dynamic SQL but unfortunately, I don't see how to save that result to a table. If someone wants to tell me how to save the results of a dynamic SELECT to a table in my schema, that would accomplish what I need.
Here is the procedure version with dynamic SQL. Also included is how I am trying to store the results into a table.
BEGIN
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(max)
set @cmd = '
(SELECT
COUNT(DISTINCT [PersonID]) AS count_person,
[' + @feature + '] AS feature, [HasT2DM] AS target
FROM
dbo.[' + @table + ']
GROUP BY
[' + @feature + '], [HasT2DM])
'
EXEC sp_executesql @cmd
END
GO
O_E_1 @feature = 'Diagnosis', @table = 'PatientDiagnoses'
SELECT *
INTO TestTable
FROM (O_E_1 @feature = 'Diagnosis', @table = 'PatientDiagnoses')
GO
I was able to code the answer I need. Here it is.
DROP PROCEDURE IF EXISTS O_E_1
GO
DROP TABLE IF EXISTS TestTable
GO
CREATE PROCEDUre O_E_1
@feature NVARCHAR(128),
@table NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cmd NVARCHAR(max)
set @cmd = '
(SELECT
COUNT(DISTINCT [PersonID]) AS count_person,
[' + @feature + '] AS feature, [HasT2DM] AS target
FROM
dbo.[' + @table + ']
GROUP BY
[' + @feature + '], [HasT2DM])
'
EXEC sp_executesql @cmd
END
GO
DROP TABLe IF EXISTS RESULT
CREATE TABLE Result
(count_person numeric,
feature varchar(128),
target varchar(128)
)
INSERT Result EXEC O_E_1 @feature = 'Diagnosis', @table = 'PatientDiagnoses'
Select TOP 100 * FROM RESULT
