How to exec a stored procedure for each row in a select statement?

Question

I have a stored procedure that returns an unique Id. I need to call this sp to get the unique ID for each row. I must use this SP because an application also uses this.

How can I select for each row a ID that is returned from the SP?

CREATE procedure [dbo].[SelectNextNumber]

@TableName nvarchar(255)
as
begin

  declare @NewSeqVal int

  set NOCOUNT ON

  update Number --This is a table that holds for each table the max ID

  set @NewSeqVal = Next = Next + Increase

  where TableNaam= @TableName

  if @@rowcount = 0 
  begin
        Insert into Number VALUES (@TableName, 1, 1) 
        return 1
  end

  return @NewSeqVal

The number table:

CREATE TABLE [dbo].[Number](
    [TableName] [varchar](25) NOT NULL,
    [Next] [int] NULL,
    [Increase] [int] NULL

I have seen that a While loop is usable for this but in my situation I don't know how to use a while loop.

Try to avoid loops if you can help it; set-based operations are usually magnitudes faster. I'd suggest looking at Row_Number() or ranking functions to generate sequential numbers - but then again I'm not sure what you're trying to achieve here; sounds like you're trying to re-invent identity columns. — Bridge
– Bridge, Commented Nov 13, 2012 at 10:42
Possible duplicate of SQL - Call Stored Procedure for each record — Michael Freidgeim
– Michael Freidgeim, Commented Nov 13, 2019 at 12:31

fnurglewitz · Accepted Answer · 2012-11-13 14:22:50Z

3

You can't use stored procedures inside a SELECT statement, only functions. You can iterate on a resultset with a cursor if you really have to use a stored procedure:

http://msdn.microsoft.com/library/ms180169.aspx

EDIT: To be honest I'm not very sure to have understood what you really need, it looks like you are building a IDENTITY by yourself ( http://msdn.microsoft.com/library/ms174639(v=sql.105).aspx ); still, if you really need to run a cursor here's an example which uses your stored procedure:

http://sqlfiddle.com/#!3/2b81a/1

edited Nov 13, 2012 at 14:22

answered Nov 13, 2012 at 10:41

fnurglewitz

2,12714 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ahmet Over a year ago

I tried but I dont knew how to.. Do you have an example for my situation?

marc_s Over a year ago

@Ahmet: there's plenty of wonderful, freely available documentation on EVERYTHING concerning SQL Server up on the MSDN site ....

RichardTheKiwi · Accepted Answer · 2012-11-13 10:46:01Z

1

Taking the singular INSERT INTO.. SELECT apart:

Temporarily store the SELECT results away

 declare @rc int, @NewSeqVal int;
 SELECT ..
   INTO #tmp -- add this
   FROM ..

Store the rowcount and get that many numbers

 set @rc = @@rowcount;

For which you have to use the code in the SP directly:

 update Number --This is a table that holds for each table the max ID
 set @NewSeqVal = Next = Next + @rc
 where TableNaam= 'sometbl';

Finally, the insert

 INSERT ... 
 SELECT ID = @NewSeqVal + 1 - row_number() over (ORDER BY col1)
       , {all the other columns}
 FROM #tmp;

ORDER by Col1 is arbitrary, choose something sensible, or make it ORDER BY NEWID() if you don't care.

answered Nov 13, 2012 at 10:46

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Collectives™ on Stack Overflow

How to exec a stored procedure for each row in a select statement?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related