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, Nov 13 '12 at 10:42
Possible duplicate of [SQL - Call Stored Procedure for each record](https://stackoverflow.com/questions/2077948/sql-call-stored-procedure-for-each-record) — Michael Freidgeim, Nov 13 '19 at 12:31

fnurglewitz · Answer 1 · 2012-11-13T14:22:50.187

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 '12 at 14:22

answered Nov 13 '12 at 10:41

fnurglewitz

2,097
14
21

I tried but I dont knew how to.. Do you have an example for my situation? – Ahmet Nov 13 '12 at 11:03
1

@Ahmet: there's plenty of [**wonderful, freely available** documentation](http://msdn.microsoft.com/en-us/library/ms180169.aspx) on **EVERYTHING** concerning SQL Server up on the MSDN site .... – marc_s Nov 13 '12 at 11:04

score 1 · Accepted Answer · answered Nov 13 '12 at 10:46

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.

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

2 Answers2