foreach loop in sql
-
I just wrote a new stored proc. The old stored proc had a LookUpId table to increment Id ..so it was updating the id by one and fetching that id to insert into the table. I just used an increment identity column with table valued parameter. Thanks
-
Dave Ballantyne (2/11/2015)
nwtsqlserv (2/11/2015)
Why is it wrong can you explain?I think there is nothing wrong in sending a comma seperated list.
Other than going through the unnecessary overhead of concatenating the string on the client side and splitting it up on the server side ( + invoking the internals of latching / logging / locking etc.. ) nothing at all.
Microsoft added table valued parameters to the sql server product for a very good reason, to pass a set of data ( which you do have ) to sqlserver in a more performant ( and easier ) way.
My question is to insert each row of the table into another table. So if I have a table valued parameter ..i still have to go through each row of that right?
Nope, you will have a set of data. Your TSQL code will simply be:
INSERT INTO <DestinationTable>
SELECT blah
FROM @<TableValuedParameter>
No looping is needed.
+1 on this answer Dave.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
nwtsqlserv (2/11/2015)
I just wrote a new stored proc. The old stored proc had a LookUpId table to increment Id ..so it was updating the id by one and fetching that id to insert into the table. I just used an increment identity column with table valued parameter. ThanksIn SQL 2012 you can use a SEQUENCE for this.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
nwtsqlserv (2/11/2015)
I already have a spitter function mate...please have a read through my question again...thanks.Could you post the code for your splitter, please?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff Moden (2/11/2015)
nwtsqlserv (2/11/2015)
I already have a spitter function mate...please have a read through my question again...thanks.Could you post the code for your splitter, please?
CREATE FUNCTION [dbo].[StringSplitter](
@String varchar(5000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE(result varchar(256))
AS
BEGIN
If LEN(@String) = 0
RETURN
DECLARE @lastpos int,@maxpos int,@len int,@nextpos int
SET @nextpos = CHARINDEX(@Delimiter,@String)
If @nextpos = 0
Begin
INSERT@RESULTS
SELECT@String
End
Else
Begin
SELECT @lastpos = -1
WHILE@nextpos <> 0
Begin
INSERT@RESULTS
SELECTLTRIM(RTRIM(SUBSTRING(@String,@lastpos,@nextpos - @lastpos)))
SELECT @lastpos = @nextpos + 1
SELECT @nextpos =CHARINDEX(@Delimiter,@String,@nextpos + 1)
If @nextpos = 0
Begin
-- INSERT THE LAST ONE
INSERT@RESULTS
SELECTLTRIM(RTRIM(SUBSTRING(@String,@lastpos,LEN(@String) + 1 - @lastpos)))
End
End
End
RETURN
END
-
WHILE@nextpos <> 0
Begin
...
End
RBAR!
Don Simpson
I'm not sure about Heisenberg. -
nwtsqlserv (2/12/2015)
Jeff Moden (2/11/2015)
nwtsqlserv (2/11/2015)
I already have a spitter function mate...please have a read through my question again...thanks.Could you post the code for your splitter, please?
CREATE FUNCTION [dbo].[StringSplitter](
@String varchar(5000),
@Delimiter char(1)
)
RETURNS @RESULTS TABLE(result varchar(256))
AS
BEGIN
If LEN(@String) = 0
RETURN
DECLARE @lastpos int,@maxpos int,@len int,@nextpos int
SET @nextpos = CHARINDEX(@Delimiter,@String)
If @nextpos = 0
Begin
INSERT@RESULTS
SELECT@String
End
Else
Begin
SELECT @lastpos = -1
WHILE@nextpos <> 0
Begin
INSERT@RESULTS
SELECTLTRIM(RTRIM(SUBSTRING(@String,@lastpos,@nextpos - @lastpos)))
SELECT @lastpos = @nextpos + 1
SELECT @nextpos =CHARINDEX(@Delimiter,@String,@nextpos + 1)
If @nextpos = 0
Begin
-- INSERT THE LAST ONE
INSERT@RESULTS
SELECTLTRIM(RTRIM(SUBSTRING(@String,@lastpos,LEN(@String) + 1 - @lastpos)))
End
End
End
RETURN
END
Thank you. Just so you know, that's what's known as an "InchWorm Splitter." Its relative performance to other methods appears as the Green line on the following chart.
If you're only using the splitter for splitting input parameters, it won't make much difference unless you're taking hundreds of hits per second but if you ever have to do any batch level splitting, you might want to consider a CLR (the Green line below) or the splitter that produces the black line in both charts, which can be found at the following URL.
http://www.sqlservercentral.com/articles/Tally+Table/72993/
In fact, the code listed includes an additional ~20% improvement compared to what is in those charts.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks I will read this article and change the function that I am using.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply