Need to run Stored Procedure in select statement

[Brad Allen]

I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

[Lia]

Brad,
I'm assuming you're doing this from an ASP page (I'm using similar code).

First execute the stored procedure and get the next value (you need a connection object, a command object, and a recordset object):

objCmd.CommandText="DECLARE @newVal int EXECUTE sp_GetVal @newVal output select newVal=@newVal"

set recSET=objCmd.Execute
myNewVal=recSET(0)
recSET.Close

Then just pass myNewVal to the insert statement.

Lia

------------
Brad Allen at 8/21/00 3:15:32 PM


I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

[HemantH]

Unfortunately you can't. Execute the proc first and get the value into a local variable and use this variable in the insert statement.


------------
Brad Allen at 8/21/00 3:15:32 PM


I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

[nr]

The getnextvalue should be something like
create procedure getnextvalue
@NextValue int output
as
update ValueTable
set ValueField = ValueField + 1 ,
@NextValue = ValueField + 1
go

so that you don't have to lock the table.

then
declare @i int
exec getnextvalue @i output
insert foo (field1,field2,field3)
select fieldx, fieldy, @i
from bar

If you are worried about the order of inserts then a transaction would lock the ValueTable so synchronising inserts.

------------
Brad Allen at 8/21/00 3:15:32 PM


I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

[Carmelo Scarpino]

Another approach is to create a linked server and issue the following command:


insert into foo (field1,field2,field3) select '1', '1', b.cnt from a,
openquery (sql7,'exec test&#39 b


NOTE: sql7 is name of linked server. b is that alias for the openquery. I do not do a join between a and b but you could if need to. b.cnt is made up of b the alias name and cnt being the name of the column in the stored procedure.

------------
Brad Allen at 8/21/00 3:15:32 PM


I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.