4

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?

4

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

|improve this answer|||||
3

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
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.