1

How a function/procedure can be called inside a insert statement without command parameters. e.g.

Insert into myTable (....) values (1,2,mySP_or_mySDF, 3,4)
bjan
  • 2,000
  • 7
  • 32
  • 64

3 Answers3

5

You can also use INSERT INTO... SELECT

INSERT INTO myTable (col1, col2, col3....)
SELECT 1, 2, mySP_or_mySDF(), 3, 4

If you want to INSERT data from a stored procedure, then you will need to create a temp table and insert the result into the temp table first, then you can use the result to insert into the final table.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • it looks function can be called but not the sp, is it? – bjan Nov 21 '12 at 14:01
  • @bjan see my edit, you will have to add the data from a stored proc to a temp table first, then you can use it since you want to insert additional values. Here is another question on SO about it -- http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure – Taryn Nov 21 '12 at 14:04
  • i want to mimic the usage of oracle's sequence in sql server, so it needs to be inside a single statement – bjan Nov 21 '12 at 14:09
  • 1
    @bjan there is no exact match of a sequence in sql server but have you looked at using `IDENTITY` then you can use `SCOPE_IDENTITY` -- see this question - http://stackoverflow.com/questions/661998/oracle-sequence-but-then-in-ms-sql-server – Taryn Nov 21 '12 at 14:11
  • And there *is* a `SEQUENCE` in SQL Server 2012. But I agree that you shouldn't be generating a "next ID" off in some other procedure when SQL Server is perfectly capable of doing that for you. – Aaron Bertrand Nov 21 '12 at 15:50
  • @AaronBertrand I didn't know SQL Server 2012 has a sequence now, that is good to know. I am guessing if they are trying to reinvent the wheel to generate a sequence they are not using 2012. – Taryn Nov 21 '12 at 15:52
  • Right, prior to SQL Server 2012 I'd still suggest an IDENTITY column over this other procedure mess. – Aaron Bertrand Nov 21 '12 at 16:10
3

try

Insert into myTable (....) values (1,2,dbo.mySP_or_mySDF(), 3,4)
John Woo
  • 258,903
  • 69
  • 498
  • 492
1

You cannot call a stored procedure within a SELECT/UPDATE/INSERT statement, with the exception of the statement SQL:

insert into <whatever>
    exec <some sql statement here>;

(and related constructs).

Stored procedures do not really return values. Well, they do return an integer. So, you could do:

declare @retval int;
exec @retval = mysp;
insert into myTable(col1, col2, col3, col4)
    select 1, 2, @retval, 3, 4

You could have the stored procedure "return" all 4 values, by including the following statement in the sp body:

select 1, 2, <whatever>, 3, 4

and then doing insert into myTable(col1, col2, col3, col4) exec mysp;

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786