July 5, 2023 at 9:25 pm
use dB
DROP PROCEDURE [dbo].[pre_update]
GO
SET QUOTED_IDENTIFIER ON
CREATE TYPE GetTrackingNo AS TABLE
(TrackingNo Int
);
go
CREATE PROCEDURE pre_update
@TVP GetTrackingNO READONLY
AS
Begin
SET NOCOUNT ON;
DECLARE
@trackingTVP AS GetTrackingtNO
,@col1 VARCHAR (MAX)
,@col2 VARCHAR(100)
,@col3 VARCHAR(MAX) your text
insert into @TrackingTVP (TrackingNO)
(select distinct TrackingNO from sub where date between '2023-01-01' and '2023-04-01');
select @col1 = col4 from dbo.report where trackingno=@TrackingTVP where condition
select @col2 = col4 from dbo.report where trackingno=@TrackingTVP where condition
select @col3 = col4 from dbo.report where trackingno=@TrackingTVP where condition
update dbo.getreport set col=@col1
,col2=@col2
,col3=@col3
where TrackingNO=@TrackingTVP
exec pre_update @trackingtvp
July 5, 2023 at 10:51 pm
Why do you have both @TVP GetTrackingNO parameter and @trackingTVP AS GetTrackingtNO variable?
It looks like the extra "t" in GetTrackingtNO is a typo, & you are actually using GetTrackingNO, correct? If so, this is the problem --
where TrackingNO=@TrackingTVP
You can't try to do an equal comparison a column value and a table-valued parameter that could have many values.
Secondarily, this
insert into @TrackingTVP (TrackingNO)
(select distinct TrackingNO from sub where date between '2023-01-01' and '2023-04-01');
should probably be
insert into @TrackingTVP (TrackingNO)
-- BETWEEN is inclusive. Do you want to include '2023-04-01', or only dates prior to that?
-- Don't need parentheses around the select statement
select distinct TrackingNO from sub where date >= '2023-01-01' and [date] < '2023-04-01';
July 6, 2023 at 3:57 pm
i changed code to below added cursor to fetch distinct values from other table and execute procedure for each of the value.
getting @trackingNo has already declared and variable name should be unique, any help how to fix this
CREATE PROCEDURE [dbo].[pre_update]
@TrackingNO INT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @col1 VARCHAR(MAX), @col2 VARCHAR(100), @col3 VARCHAR(MAX);
SELECT @col1 = col4 FROM dbo.report WHERE trackingno = @TrackingNO AND condition;
SELECT @col2 = col4 FROM dbo.report WHERE trackingno = @TrackingNO AND condition;
SELECT @col3 = col4 FROM dbo.report WHERE trackingno = @TrackingNO AND condition;
UPDATE dbo.getreport SET col = @col1, col2 = @col2, col3 = @col3 WHERE trackingno = @TrackingNO;
END;
DECLARE @TrackingNO INT;
DECLARE cursorElement CURSOR FOR
SELECT TrackingNO FROM dbo.sub WHERE date BETWEEN '2023-01-01' AND '2023-04-01';
OPEN cursorElement;
FETCH NEXT FROM cursorElement INTO @TrackingNO;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC [dbo].[pre_update] @TrackingNO;
FETCH NEXT FROM cursorElement INTO @TrackingNO;
END
CLOSE cursorElement;
DEALLOCATE cursorElement;
July 12, 2023 at 10:58 am
You are declaring @TrackingNO as a variable when there is already a parameter by that name in the procedure declaration.
July 18, 2023 at 5:34 pm
This was removed by the editor as SPAM
July 20, 2023 at 5:59 am
You need to change the code so as to work it well.
There were some errors within the code that I have added:
USE dB
GO
DROP PROCEDURE IF EXISTS [dbo].[pre_update]
GO
SET QUOTED_IDENTIFIER ON
GO
-- Create a User-Defined Table Type
CREATE TYPE GetTrackingNo AS TABLE
(
TrackingNo INT
);
GO
-- Create the stored procedure
CREATE PROCEDURE pre_update
@TVP GetTrackingNo READONLY
AS
BEGIN
SET NOCOUNT ON;
DECLARE @col1 VARCHAR(MAX)
, @col2 VARCHAR(100)
, @col3 VARCHAR(MAX);
-- Select the appropriate columns from dbo.report table based on the condition
SELECT @col1 = col1, @col2 = col2, @col3 = col3 FROM dbo.report WHERE trackingno IN (SELECT TrackingNo FROM @TVP);
-- Update dbo.getreport with the values obtained from dbo.report
UPDATE gr
SET gr.col1 = @col1, gr.col2 = @col2, gr.col3 = @col3
FROM dbo.getreport gr
INNER JOIN @TVP tvp ON gr.TrackingNO = tvp.TrackingNo;
END
GO
-- Now you can execute the stored procedure with a table variable
DECLARE @trackingTVP AS GetTrackingNo;
INSERT INTO @trackingTVP (TrackingNo)
SELECT DISTINCT TrackingNO FROM sub WHERE date BETWEEN '2023-01-01' AND '2023-04-01';
EXEC pre_update @TVP = @trackingTVP;
I hope this will work for you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply