Use name of table as input in procedure and store the output of SELECT statement in output variable SQL

Question

I am trying to create a procedure, that will accept as input a variable called @tablename and will return as output the value of a variable called @myoutput. As the names of the variables signify, @tablename should be the name of a table and @myoutput should be just be the sum of the the first column of the table specified by @tablename, divided by the number 3.3. The procedure I have written is the following:

CREATE PROCEDURE sample_proc
@tablename VARCHAR(MAX),
@myoutput DECIMAL(20,5) OUTPUT
AS
SET NOCOUNT ON;
 @myoutput=EXEC('SELECT SUM('+@tablename+'.column1)/3.3)'

When I just use EXEC(), the select statement is executed as it should, but I cannot get the value I want from the table. What I want, is to store the value I calculate in the variable @myoutput, and export that to my main script. The above code produces an error. Does anyone know how I can implement this?

Answer 1

First you're not using a valid syntax due your SELECT command has no FROM clause.

Then you should use sp_executesql just to take care of the output parameters.

create table xx (id int, column1 int);
create table yy (id int, column1 int);
insert into xx values (1, 1),(2,2),(3,3);
insert into yy values (1,10),(3,30),(4,40);
GO

6 rows affected

CREATE PROCEDURE sample_proc
(
 @tablename VARCHAR(MAX),
 @myoutput DECIMAL(20,5) OUTPUT
)
AS
 SET NOCOUNT ON;
 DECLARE @Cmd NVARCHAR(MAX);
 DECLARE @ParmDefinition NVARCHAR(500);
 SET @ParmDefinition = '@Total decimal(20,5) OUTPUT';
 SET @Cmd = 'SELECT @Total = (SUM(column1)/3.3) FROM [' + @tablename + ']';
 -- just to show SQL command.
 SELECT @Cmd;
 EXEC sp_executesql @Cmd, @ParmDefinition, @Total = @myoutput out;
GO

✓

DECLARE @op DECIMAL(20,5);
EXEC sample_proc 'xx', @op OUT
SELECT @op;
EXEC sample_proc 'yy', @op OUT
SELECT @op;
GO

| (No column name) |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [xx] |
| (No column name) |
| :--------------- |
| 1.81818 |
| (No column name) |
| :------------------------------------------- |
| SELECT @Total = (SUM(column1)/3.3) FROM [yy] |
| (No column name) |
| :--------------- |
| 24.24242 |

dbfiddle here

Answer 2

You can use sp_executesql to get output parameter from dynamic sql:

alter PROCEDURE dbo.sample_proc
 @tablename sysname,
 @myoutput DECIMAL(20,5) OUTPUT
AS
SET NOCOUNT ON;
 declare @sql nvarchar(4000) = 'select @myoutput = SUM(column1)/3.3 from ' + @tablename;
 declare @params nvarchar(100) = N'@myoutput DECIMAL(20,5) output';
 exec sp_executesql @sql, @params, @myoutput= @myoutput output;
go