Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

parameter type table

Hi experts, i have this store procedure
CREATE PROC Certifica.ProcesarInformacion(
      @Codigos XML
)
AS
BEGIN
      UPDATE Certifica.ActividadesDiscentes
      SET FirmaEscaneadaDA = '1', FirmaEscaneadaDB = '0'
      FROM Certifica.ActividadesDiscentes A
      INNER JOIN @Codigos.nodes('/values/value') AS T(c)
      ON A.CodigoActividadesDiscentes = c.value('@id', 'INT')
END

I want to replace the type parameter of type xml and use parameter type table, can you help?
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of enrique_aeo
enrique_aeo

ASKER

if you help me with the code would be excellent
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ok
CREATE TYPE miTipoTabla AS TABLE
(
      codigoActividadesDiscentes int
)

CREATE TABLE [dbo].[MiTablaPersistente] (
      [correlativo] [int] NOT NULL identity PRIMARY KEY,
      codigoActividadesDiscentes int
      )

CREATE PROC usp_AddRowsToMyTable @MyTableParam miTipoTabla READONLY
      as
      INSERT INTO [MiTablaPersistente](codigoActividadesDiscentes)
      SELECT codigoActividadesDiscentes
      FROM @MyTableParam

I need to update stored procedure
1. I pass a list of correlated
2. I updated with the value 0
something like that
UPDATE MiTablaPersistente
SET codigoActividadesDiscentes = 0
WHERE correlativo = [parameter type table];
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is the code
ALTER PROC [dbo].[usp_ActualizarContactos]  @MyTableParam miTipoTabla READONLY
AS
UPDATE Person.Contact
SET Suffix = 'Updated0'
WHERE ContactID IN ( select ContactID  
                               from @MyTableParam
                               where ContactID  is not null );

select top 0 ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion
from Person.Contact


/****** Test ******/
DECLARE @MiTabla miTipoTabla
INSERT INTO @MiTabla(ContactID)
VALUES (1),
         (2),
         (3),
         (4),
         (5)
--select ContactID  
--from @MiTabla
--where ContactID  is not null

EXEC usp_ActualizarContactos @MyTableParam = @MiTabla
/****** Test ******/
I agree