CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
erasmo carlos
erasmo carlos

Reputation: 682

T-SQL: execute procedure obtaining parameter value from table (SQL Server 2012)

I have a stored procedure that receives an ID parameter and performs insert/ updates.

I have about 25K records that need to be updated and I have been trying to figure out a way to enhance the single parameter stored procedure, so that it can process multiple values, or sets of Ids coming from a table.

The closest I gotten to do this, is by creating a cursor and looping through it whilst executing the stored procedure, unfortunately, I have come to realize that that is a very slow way of doing this.

I was wondering if I could get help modifying my stored procedure so that it can have a better performance.

Here is my code that uses a cursor. 

SET NOCOUNT ON;
DECLARE @Id    VARCHAR(32);

DECLARE csr CURSOR
FOR SELECT DISTINCT ID
    FROM table
    ORDER BY id;

OPEN csr;

FETCH NEXT FROM csr INTO
    @Id;

WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Running for '+@Id;
        EXEC update_product_xml_single_id @Id;
        FETCH NEXT FROM csr INTO
            @Id;
    END;

CLOSE csr;
DEALLOCATE csr;

UPDATE

Following recommendation made by objectNotFound:

--Create User-defined Table Type
CREATE TYPE IdTableType AS TABLE ([Id] VARCHAR(20) NOT NULL);

-- Modified old stored proc: update_product_xml_single_id, to receive the new type
CREATE PROCEDURE update_product_xml_tvp (@id IdTableType READONLY)

-- Called stored proc with a table variable
DECLARE @ID IdTableType

INSERT @ID 
SELECT DISTINCT ID
FROM Products
WHERE ID LIKE 'N0%'
ORDER BY ID;

EXEC update_product_xml_tvp  @ID

The problem that I am facing is with the original stored procedure, or step #2. The only thing I modified was the name and the parameter type, but when I try to save the changes, I get errors like:

Msg 137, Level 16, State 1, Procedure update_product_xml_tvp, Line 43 Must declare the scalar variable "@id".

This scalar variable that the message is complaining about, is the same parameter that I just modified from varchar to table type. I am not sure how to proceed form here, or if I got the steps wrong. Any help is great, thank you.

Upvotes: 0

Views: 970

Answers (2)

objectNotFound
objectNotFound

Reputation: 1783

What you need is Table Valued Parameter as input to your stored procedure (update_product_xml_single_id). So instead of passing a scalar value of type int (@Id) you will pass a list ( say @TVP ) that contains all the ID's that you are currently processing from the table "table" in your OP. And inside your stored procedure you can use that table variable @TVP just like any other ordinary table.

This Links shows you how to do it with an example (I have copied that example down below ):

https://msdn.microsoft.com/en-us/library/bb510489.aspx?f=255&MSPPError=-2147217396

Example from the above msdn link: 

USE AdventureWorks2012;  
GO  

-- Step 1
/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

-- Step 2
/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE dbo. usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
BEGIN
    SET NOCOUNT ON  
    INSERT INTO AdventureWorks2012.Production.Location  
           (Name  
           ,CostRate  
           ,Availability  
           ,ModifiedDate)  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
  END
  GO

Usage Of passing the TVP to the Stored Procedure:

/* Declare a table variable that references the type. */  
DECLARE @LocationTVP AS LocationTableType;  -- This was created in Step 1 above.

/* Add data to the table variable. So this would be the same as your Declare Cursor SQL except its inserting into a Table Valued Parameter @LocationTVP */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT Name, 0.00  
    FROM AdventureWorks2012.Person.StateProvince;  

/* Pass the table variable data in @LocationTVP to a stored procedure that now expects a Table valued parameter as input */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO

EDIT:

I just tested your CREATE SP Code and it works fine this is what I tried

CREATE TYPE IdTableType AS TABLE ([Id] VARCHAR(20) NOT NULL);
GO

-- Modified old stored proc: update_product_xml_single_id, to receive the new type
CREATE PROCEDURE update_product_xml_tvp 
( @id IdTableType READONLY)
AS
BEGIN
     SELECT * FROM  @id --- Obviously you will be using it differently but this is just to prove that it works
END

Also the "INTO" is missing in your insert statement

This:

INSERT @ID 
SELECT DISTINCT ID
FROM Products
WHERE ID LIKE 'N0%'
ORDER BY ID;

Should be 

INSERT INTO @ID 
SELECT DISTINCT ID
FROM Products
WHERE ID LIKE 'N0%'
ORDER BY ID;

I added the INTO part to my code. But it is still giving me the same error. I want to make sure that you know that the stored proc update_product_xml_tvp is the same stored procedure that I was using with the CURSOR, that the only thing I changed in it was the input parameter type. It used to be: @pf_id VARCHAR(100)

and now it is: @id id READONLY

Since I did not modified anything in the code in that procedure, the error comes from sections like: WHERE ai.ID = @id

Something does not look right, like @id is no longer the same data type as ai.id hence the comparison is no longer valid, but I do not know how to correct it, or where.

So to make sure this is right, I ended up with

 1. A user defined table type named id

 CREATE TYPE idTableType AS TABLE ([some_id] VARCHAR(20) NOT NULL);

 2. A stored procedure that populates that table type:

 CREATE PROC [dbo].[mn_pass_id_table] ( @id_spVariable idTableType READONLY )
 AS
 BEGIN
     SET NOCOUNT ON
     SELECT DISTINCT ID
     FROM Products
     WHERE ID LIKE 'N0%'
     ORDER BY ID;
 END

 3. And a modification to the stored procedure that existed already.

    From:
    ALTER PROCEDURE update_product_xml_single ( @id VARCHAR(100))

    To:       
    ALTER PROCEDURE update_product_xml_tvp (@id_spVariable idTableType READONLY)

Inside this procedure there are 4 WHERE conditions that I did not changed, nor modified, and that are giving me errors that won't let me update the stored procedure:

WHERE ai.ID  IN ( Select some_id from  @id_spVariable  )

My understanding is that in the end, I will be able to do the following from a new query window:

DECLARE @ID_Var_That_We_Will_Pass_To_Your_SP as idTableType 

INSERT INTO  @ID_Var_That_We_Will_Pass_To_Your_SP 
SELECT SOME_COLUMN FROM REAL_TABLE  ( This will be the one that you currently use to Declare the Cursor )

EXEC update_product_xml_tvp  @ID_Var_That_We_Will_Pass_To_Your_SP

I am sure I am messing something up here. I am really trying to get it right. Thank you for your patience and help.

Upvotes: 2

abcool
abcool

Reputation: 95

I am beginner but i think this will help you give it a try .

create user-defined function

<--step 1:-->

create type [dbo].[IdTableType ] as table 
(
[Id] BIGINT NOT NULL
)
GO

now <--step 2:-->

CREATE PROCEDURE update_product_xml_tvp 
    (
    @table1 IdTableType  READONLY
    )
AS
BEGIN TRY
Declare  @ID BIGINT

 INSERT into YOUR_TABLE 
  (
   ID
   )
 SELECT DISTINCT ID
 FROM @table1
 WHERE ID LIKE 'N0%'
 ORDER BY ID;

END TRY
BEGIN CATCH
print error_message()
END CATCH

and call it through array

Upvotes: 0

Related Questions