CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ahmet
Ahmet

Reputation: 59

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

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.

Upvotes: 1

Views: 4856

Answers (2)

fnurglewitz
fnurglewitz

Reputation: 2127

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

Upvotes: 3

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

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.

Upvotes: 1

Related Questions