enrique_aeo
asked on
parameter type table
Hi experts, i have this store procedure
CREATE PROC Certifica.ProcesarInformac ion(
@Codigos XML
)
AS
BEGIN
UPDATE Certifica.ActividadesDisce ntes
SET FirmaEscaneadaDA = '1', FirmaEscaneadaDB = '0'
FROM Certifica.ActividadesDisce ntes A
INNER JOIN @Codigos.nodes('/values/va lue') AS T(c)
ON A.CodigoActividadesDiscent es = c.value('@id', 'INT')
END
I want to replace the type parameter of type xml and use parameter type table, can you help?
CREATE PROC Certifica.ProcesarInformac
@Codigos XML
)
AS
BEGIN
UPDATE Certifica.ActividadesDisce
SET FirmaEscaneadaDA = '1', FirmaEscaneadaDB = '0'
FROM Certifica.ActividadesDisce
INNER JOIN @Codigos.nodes('/values/va
ON A.CodigoActividadesDiscent
END
I want to replace the type parameter of type xml and use parameter type table, can you help?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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](codig oActividad esDiscente s)
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];
CREATE TYPE miTipoTabla AS TABLE
(
codigoActividadesDiscentes
)
CREATE TABLE [dbo].[MiTablaPersistente]
[correlativo] [int] NOT NULL identity PRIMARY KEY,
codigoActividadesDiscentes
)
CREATE PROC usp_AddRowsToMyTable @MyTableParam miTipoTabla READONLY
as
INSERT INTO [MiTablaPersistente](codig
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
WHERE correlativo = [parameter type table];
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is the code
ALTER PROC [dbo].[usp_ActualizarConta ctos] @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 ******/
ALTER PROC [dbo].[usp_ActualizarConta
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 ******/
ASKER
I agree
ASKER