December 19, 2016 at 6:41 pm
Hi,
I have created a stored procedure(1) to do an insert / update when a parameter is passed. i have a table that stores name and age, and a stored procedure that has 2 parameters - name, age. The stored procedure will find the name and if it finds it updates the age otherwise it will insert the name and age.
I have created another stored procedure(2) that query all the names and age from a different source table so I have the result set. How can I iterate on the result then execute procedure(1) on each row? Or is there a better alternative to solve my issue?
Thank.
gbp
December 19, 2016 at 7:33 pm
Could you post your code? Not sure why you have ot itereate over the results and do something... trying to figure out a way to avoid using a cursor.
December 19, 2016 at 8:19 pm
Here's my code:
--
-- first procedure
--
CREATE PROCEDURE sp_IU_NamesTbl
(
@Age[int] = NULL,
@Name[varchar](30) = NULL
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @RowId int=0;
BEGIN TRY
SELECT
@RowId = [Age]
FROM
NameMaster
WHERE
( Name = @Name );
IF (@RowId = 0) AND (@Name IS NOT NULL)
INSERT INTO [NameMaster]( Name, Age )
VALUES(@Name, @Age);
ELSE
IF (@RowId > 0) AND (@SupplierTypeName IS NOT NULL)
UPDATE [NameMaster] SET
Name = @Name
, Age = @Age
WHERE ( Name = @Name );
SELECT @@ROWCOUNT AS 'NumberOfRecords';
END TRY
BEGIN CATCH
-- Raise an error
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
RAISERROR(@ErrMsg, @ErrSeverity, 1);
END CATCH
GO
--
-- 2nd procedure
--
CREATE PROCEDURE sp_SelectNames
( @TrnDatetime datetime )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @RowId int=0;
BEGIN TRY
SELECT
Names, age
FROM
NameSource
WHERE
( datetime = @Datetime );
--
-- With the result of the above query i want to
-- iterate and call the 1st procedure pseudo code like below
--
-- query.first;
-- while query result not eof()
-- {
-- exec sp_IU_NamesTbl( query.names, query.age );
-- query.next;
-- }
--
END TRY
BEGIN CATCH
-- Raise an error
DECLARE @ErrMsg NVARCHAR(4000), @ErrSeverity INT;
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
RAISERROR(@ErrMsg, @ErrSeverity, 1);
END CATCH
GO
I've posted as everyone does not encourage the use of a cursor, and we always use stored procedure to do our stuff.
Thanks.
gp
December 19, 2016 at 9:09 pm
How about modifying your proc to have a TVP (table valued parameter) as an input ?
I *think* thats what you are after - someone smarter than me may have a better idea though 🙂
December 20, 2016 at 10:47 am
gp-sql-novi (12/19/2016)
Hi,I have created a stored procedure(1) to do an insert / update when a parameter is passed. i have a table that stores name and age, and a stored procedure that has 2 parameters - name, age. The stored procedure will find the name and if it finds it updates the age otherwise it will insert the name and age.
I have created another stored procedure(2) that query all the names and age from a different source table so I have the result set. How can I iterate on the result then execute procedure(1) on each row? Or is there a better alternative to solve my issue?
Thank.
gbp
I guess the question is, why do you need any parameters at all? There are at least two good ways to solve the problem, one of which doesn't even require a stored procedure. The first is to use the MERGE statement. It can be finicky, but it does work. Your stored procedure just references the source data table and the destination table within the MERGE statement, which you can look up in Books OnLine (aka BOL). Alternatively, the 2nd option is to use a MERGE JOIN within an SSIS package.
I ask because you haven't mentioned the why behind what you need to do.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 20, 2016 at 2:05 pm
I have simplified the procedure just to show 1 scenario, but on the real world, the source has a lot of data that needs to go to different table. The approach on this one will be same on other table but using other fields from the source.
So the thinking was to query all fields from the source and then iterate and call 3 or 4 stored procedures to populate multiple tables.
December 20, 2016 at 2:36 pm
Slight detour here. first I notice you name your procedure with the sp_ prefix. This is a bad idea. I would prefer no prefix at all but if you insist it should be something else. https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix
Second, you really should never store a person's age. The instant the data hits your table it is stale. If you need to know somebody's age you should instead store their birthdate and calculate age when you need it.
The next big red flag here is your use of READ UNCOMMITTED. This is very dangerous and downright awful in a stored procedure. This is just like adding NOLOCK to every single table inside your procedure. Yes there are times when this is ok but when you need accurate information this simply is not a good idea. http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/[/url]
Then you have the issue of looping. I would create a procedure that doesn't require RBAR processing for this kind of thing. It is simply insert statements. You should be able to add some grouping to capture the aggregate information without resorting to loops. If you can post ddl and sample information I can help you find a better approach here than loops.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2016 at 8:15 pm
Below are my tables, source table would be the source of all information that will get breakdown into 4 different tables as Name table, company, F1 and F2 tables. As we can see on the sample, I want to extract the information from source and insert/update their corresponding tables.
Destinations tables has a stored procedures that will check if value from the source exists then update otherwise insert the value.
Not sure how to do it without using cursor as everyone is discouraging the use of it. As I have posted here (earlier posting), I was thinking of a loop on the source and calling each of stored procedure to populate the destinations tables.
SOURCE TABLE
---------------------------------------------------
F1 F2F3COMF5NAMEAGE
--+---+---+---+----+----+-------+
1 |2A|A |XX|MMM|FOO|5 |
1 |2B|A |YY|MMM|FO2|4 |
1 |2C|B |XX|MMM|FOO|5 |
2 |2A|A |XX|MMM|FOO|5 |
3 |2B|A |YY|MMM|FO2|4 |
1 |2B|B |XX|MMM|FO3|7 |
--+---+---+---+----+----+-------+
Destination Tables
NAME TABLE
------------+
NAME |AGE |
FOO |5 |
FO2 |4 |
FO3 |7 |
+----+------+
COMPANY TABLE (COM COLUMN)
-------------
XX
YY
F1 TABLE
--------
1
2
3
F2 TABLE
--------
2A
2B
2C
If you could point me where to start, it would really be much appreciated.
Thanks.
December 20, 2016 at 8:24 pm
Seriously? All of these follow a similar pattern... this one:
SELECT DISTINCT field1, field2
FROM SourceTable;
Boggles my mind you could ask a reasonably complicated question and then trip up on the part that's just incredibly simple.
If you're doing this in SSIS, you could create SQL statements to insert the results of queries like this into the proper destination tables. Dead simple.
December 21, 2016 at 6:50 am
Use of Merge seems a good match for what you are asking.
However, I am baffled by what you are trying to accomplish with this. I can't think of any design pattern that would match this, except maybe populating lookup tables for an application. I would highly recommend you get a DB professional to take a look at your design as your description raises some big red flags.:unsure:
Regardless, here is your data using a merge statement:
DECLARE @Source TABLE
( F1 INT, F2 CHAR(2), F3 CHAR(1), COM CHAR(2), F5 CHAR(3), NAME CHAR(3), Age INT);
DECLARE @Name TABLE
( Name CHAR(3), Age INT);
INSERT INTO @Source
( F1, F2, F3, COM, F5, NAME, Age)
VALUES
(1, '2A', 'A', 'XX', 'MMM', 'FOO', 5),
(1, '2B', 'A', 'YY', 'MMM', 'FO2', 4),
(1, '2C', 'B', 'XX', 'MMM', 'FOO', 5),
(2, '2A', 'A', 'XX', 'MMM', 'FOO', 5),
(3, '2B', 'A', 'YY', 'MMM', 'FO2', 4),
(1, '2B', 'B', 'XX', 'MMM', 'FO3', 7);
SELECT *
FROM @Source AS s;
INSERT INTO @Name
( Name, Age)
VALUES
--populate some values so the merge does an update & a delete
('FO2', 1),
('F05',20);
SELECT *
FROM @Name AS n;
MERGE INTO @Name AS TGT
USING (SELECT DISTINCT s.NAME, s.Age FROM @Source AS s) AS SRC
ON TGT.Name = SRC.NAME
WHEN MATCHED THEN
UPDATE SET TGT.Age = SRC.Age
WHEN NOT MATCHED BY TARGET THEN
INSERT
( Name,
Age
)
VALUES
(SRC.NAME, SRC.Age)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action,
inserted.*,
deleted.*;
Wes
(A solid design is always preferable to a creative workaround)
December 21, 2016 at 11:11 am
Thank you, i know it would be simple but wanted to get solution other than using cursor.
Cheers.
December 21, 2016 at 11:14 am
Thanks. Yes it would be done to populate look up tables and the project are part of the an integration solution. The source was just part of an automated collected data that's why it's done this way.
I will have a play with your code and will come back.
Cheers.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply