SELECT @StoredProc, NextField1, NextField2 FROM ....

[KPoggensee]

I have a proprietary application that generates its own IDs internally. I have replicated the logic in a Stored Procedure.

If I issue:
EXEC MyNewID @TableName = 'Account'
I get the ID correctly back.


I now need to impliment this in an INSERT INTO ....
I'm only trying to select the records that I want to insert first but am having problems calling the Proc.

Here is what I got:
SELECT EXEC MyNewID @TableName = 'Account',
AccountName,
AccountType,
AccountWebAddress
FROM ImportStagingTable

Here is what I get:
"Incorrect Syntax near the keyword 'EXEC'

So here is my question?
Is it possible to call a Stored Proc in a SELECT statement like I am trying to do, to use in an INSERT INTO...

If so, How? If not what are my alternatives?

Thanks in advance for you advise.

 

[KPoggensee]

...I would create it as a function but it modifies a Keys table, and functions will not allow for that (Unless there is a way around that?)

 

[swampBoogie]

Why don't you use a identity column or the newid function? Both of these features handles generation of unique values.

 

[KPoggensee]

... Because it is a propritary ID that needs to be in the format normally given by the application itself, or other things will break. Internally (The app) when it creates IDs for the table it bumps a value in their Keys table for the given TableName. This is what I am having a problem doing with the function.

My other idea was to run the working stored proc I have through a cursor instead of using INSERT INTO... It's not what I wanted since a function I can throw anywhere in a SQL statement, but it will probably do (Trying now...)

Thanks SwampBoogie for your excellent suggestions in the past.

 

[bprego]

So if I understand you, The stored procedure runs and returns a value that you want to use in a select into statement??

have you tried something like

'call the procedure
EXEC MyNewID @TableName = 'Account'
'then use the return value in the select or insert into
where 'Account' is the return value.

 

[iker3000]

Result sets of stored procedures can't be used in select statements unless placed in a table. However there are many other ways to return data from the stored procedure.

1. Return value throug a result set and insert it into a table (something like exec into). Be careful when doing so, because this statement can't be nested.
2. You are using a return value
3. You are using an output variable:

In the third case MyNewID would look something like this:

CREATE PROCEDURE MyNewID
@TableName varchar(100),
@NewID int OUTPUT
AS
BEGIN
'here you must set @NewID to the required value

END

The Statement

DECLARE @NewID int
EXEC MyNewID 'Account', @NewID OUTPUT
SELECT @NewID,
AccountName,
AccountType,
AccountWebAddress
FROM ImportStagingTable

Be careful however. The sample you wrote would insert all record of ImportStagingTable with the same ID. (If you would like to import all records with different IDs, the procedure will be much more complicated - you would have to use a CURSOR for example).

 

[PHV]

Why not use a Trigger on insert ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884