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

sql serversql-server-2012

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?

Best Answer

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

Related Solutions

Sql-server – sql server Group by with calculated values

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO

SELECT
  FirstName,
  ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
       p.id,
       p.name,
       p.surname,
       p.adress,
       a.validity_name,
       b.validity_surname,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
        CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
        CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress

Sql-server – Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users

Yes, each user will get their own copy of the #temp table, even if they run at the exact same time.

(However, don't use global ##temp tables, signified with two leading pound/hash signs.)

But why do you need a #temp table here at all? Something like this should work (untested, as I don't have LDAP anywhere near me):

CREATE PROCEDURE dbo.stp_adlookup -- ALWAYS use schema prefix
  @user varchar(100),
  @objectGUID varbinary(256) OUTPUT
AS
BEGIN -- use body wrappers
  SET NOCOUNT ON;

  DECLARE @qry nvarchar(max); -- don't use CHAR for dynamic SQL

  SET @qry = N'SELECT @o = objectGUID
    FROM openquery(ADSI, ''SELECT  objectGUID              
      FROM    ''''LDAP://mydomaincontroller.com''''
      WHERE sAMAccountName = ''''' + @user + ''''''')';

  -- can probably parameterize the above, but those single
  -- quotes are a nightmare. Not sure if they're necessary
  -- but I do not feel like trying to untangle them.

  EXEC sys.sp_executesql @qry, N'@o UNIQUEIDENTIFIER', @o = @objectGUID OUTPUT;

  -- SET NOCOUNT OFF; -- don't do this.
END
GO

Best Answer

Related Solutions

Sql-server – sql server Group by with calculated values

Sql-server – Is it advisable to use a temp table when the stored procedure could be run simultaneously by different users

Related Question