CODEWITHSUNDEEP
sqlsql-serverssms
Jimmy Sgrazzutti
Jimmy Sgrazzutti

Reputation: 99

Passing a table variable to a stored procedure

I am seeking to create a procedure that I can pass a one column table, and the procedure will output the median. Right now I have a procedure that will determine the median; however, I am getting errors that my @table table variable has not been declared and that the stored procedure could not be found.

My median procedure:

CREATE OR ALTER PROCEDURE dbo.median
    (@table NUMERIC, 
     @median FLOAT OUTPUT)
AS 
    DECLARE @size AS NUMERIC
    SET @size = (SELECT COUNT(*) FROM @table)

    SET @median = (SELECT AVG(1) FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY 1) AS ROW FROM @table) AS subquery
        WHERE subquery.ROW = ROUND(@size / 2, 0) OR subquery.ROW = ROUND(@size / 2, 0, 1))
    RETURN
GO

Calling the procedure:

DECLARE @Arsenic TABLE(Ar FLOAT)
INSERT INTO @Arsenic SELECT Arsenic from dbo.HubspotWaterTestAverages

EXEC dbo.median (SELECT Arsenic FROM dbo.HubspotWaterTestAverages)

NOTE: Arsenic represents the Arsenic level results from water tests, and the values range from null to 10

The working procedure is expected to just return the median value for the column, and later on I am planning on cross joining that to a master table. Thank you for any help!

Upvotes: 1

Views: 4976

Answers (1)

Khairul Alam
Khairul Alam

Reputation: 1346

This may help. Except table variable have to use Table Type.

CREATE TABLE Employee 
(  
EmpId int NOT NULL,  
EmployeeName nvarchar(MAX),  
)
GO

CREATE TYPE EmployeeType AS TABLE  
(  
EmpId int NOT NULL,  
EmployeeName nvarchar(MAX)  
)
GO

enter image description here

CREATE  PROCEDURE PassTableTypeIntoProcedure(@EmployeeType EmployeeType READONLY)
AS
BEGIN
       INSERT INTO Employee
       SELECT * FROM @EmployeeType
END
GO

    DECLARE @EmployeeTypeVariable AS EmployeeType
    INSERT INTO @EmployeeTypeVariable  VALUES
    (1,'A'),
    ( 2,'B')

    EXEC PassTableTypeIntoProcedure @EmployeeTypeVariable
    GO
    SELECT * FROM Employee

enter image description here

Upvotes: 2

Related Questions