execute stored procedure using SELECT

I'm trying to extract data from a table in one database to insert into a table another database.

There is a pre-written stored procedure for the insert, which takes multiple parameters, and I must use this since it also updates audit tables.

What I currently have is a select statement which returns the correct values, but I'm not sure how I can now pass these values to the stored procedure. I'd hoped I could use the select statement directly within the stored procedure execute command, but it seems this isn't possible.

Could anyone suggest how I might best achieve the above?

EDIT - My current SQL is:

DECLARE @client INT, @Fee_Earner INT, @RecordType NVARCHAR(10), @RecordDate DATETIME, @Abstract NVARCHAR(500), @Comments NVARCHAR(500)

SELECT
@client = HC.CLIENT_UNO,
@Fee_Earner = HP.EMPL_UNO,
@RecordType = 'WILL ',
@RecordDate = W.WILLDATE1,
@Abstract = W.OTHERDOC1 + ' '  + W.OTHERDOC2 + ' ' + W.OTHERDOC3,
@Comments = W.NOTES
FROM
devpmsql.cmsnet_dev.dbo.HBM_CLIENT HC
RIGHT OUTER JOIN <DBNAME>.WILLS W ON W.AN COLLATE DATABASE_DEFAULT = HC.CLIENT_CODE COLLATE DATABASE_DEFAULT
LEFT OUTER JOIN <DBNAME>.HBM_PERSNL HP ON HP.EMPLOYEE_CODE COLLATE DATABASE_DEFAULT = W.PARTNER COLLATE DATABASE_DEFAULT

PRINT @client
PRINT @Fee_Earner
PRINT @RecordType
PRINT @RecordDate
PRINT @Abstract
PRINT @Comments

Thanks, Gavin

Solution

To answer your question, if you must loop through results, then you can use a cursor:

DECLARE @client INT, 
        @Fee_Earner INT, 
        @RecordType NVARCHAR(10) = 'WILL', 
        @RecordDate DATETIME, 
        @Abstract NVARCHAR(500), 
        @Comments NVARCHAR(500);


DECLARE ClientCursor CURSOR LOCAL STATIC FAST_FORWARD
FOR
SELECT  HC.CLIENT_UNO, 
        HP.EMPL_UNO, 
        W.WILLDATE1,
        Abstract = W.OTHERDOC1 + ' '  + W.OTHERDOC2 + ' ' + W.OTHERDOC3,
        W.NOTES
FROM    devpmsql.cmsnet_dev.dbo.HBM_CLIENT HC
        RIGHT OUTER JOIN <DBNAME>.WILLS W 
            ON W.AN COLLATE DATABASE_DEFAULT = HC.CLIENT_CODE COLLATE DATABASE_DEFAULT
        LEFT OUTER JOIN <DBNAME>.HBM_PERSNL HP 
            ON HP.EMPLOYEE_CODE COLLATE DATABASE_DEFAULT = W.PARTNER COLLATE DATABASE_DEFAULT


OPEN ClientCursor;
FETCH NEXT FROM ClientCursor INTO @client, @Fee_Earner, @RecordDate, @Abstract, @Comments;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXECUTE dbo.YourStoredProcedure @client, @Fee_Earner, @RecordType @RecordDate, @Abstract, @Comments;

    FETCH NEXT FROM ClientCursor INTO @client, @Fee_Earner, @RecordDate, @Abstract, @Comments;

END

I must stress the importance of declaring your cursor properly. Cursors are usually best avoided, and if there is a set based approach then it should be taken, however Cursors get an even worse reputation because the are not used properly. If, as above you only want to read access your records locally, only read them and only move forward in the cursor, then tell it this, that way memory is not allocated for performing tasks you have no intention of performing.

I should point out (although you have said you can't modify the procedure, others that read this quesiton may be able to) that the best way to actually pass a result to a stored procedure is to use table-valued parameters. The first step would be to create your type:

CREATE TYPE dbo.YourTypeName AS TABLE
(
    client INT, 
    Fee_Earner INT, 
    RecordType NVARCHAR(10), 
    RecordDate DATETIME, 
    Abstract NVARCHAR(500), 
    Comments NVARCHAR(500)
);

Then your procedure would be something like:

CREATE PROCEDURE dbo.InsertValues @NewValues dbo.YourTypeName READONLY
AS
BEGIN
    INSERT dbo.YourTable (Client, Fee_Earner, RecordType, RecordDate, Abstract, Comments)
    OUTPUT inserted.Client, #'Created', GETDATE() INTO dbo.YourAuditTable (Client, EventName, CreatedDate)
    SELECT Client, Fee_Earner, RecordType, RecordDate, Abstract, Comments
    FROM NewValues;

END

I have included the `OUTPUT' Clause because you mentioned audit tables, and it is usually the best way to access the data that has been inserted.

Finally, calling your procedure you would use something like:

DECLARE @T dbo.YourTypeName;
INSERT @T (Client, Fee_Earner, RecordType, RecordDate, Abstract, Comments)
SELECT  HC.CLIENT_UNO, 
        HP.EMPL_UNO, 
        RecordType = 'WILL',
        W.WILLDATE1,
        Abstract = W.OTHERDOC1 + ' '  + W.OTHERDOC2 + ' ' + W.OTHERDOC3,
        W.NOTES
FROM    devpmsql.cmsnet_dev.dbo.HBM_CLIENT HC
        RIGHT OUTER JOIN <DBNAME>.WILLS W 
            ON W.AN COLLATE DATABASE_DEFAULT = HC.CLIENT_CODE COLLATE DATABASE_DEFAULT
        LEFT OUTER JOIN <DBNAME>.HBM_PERSNL HP 
            ON HP.EMPLOYEE_CODE COLLATE DATABASE_DEFAULT = W.PARTNER COLLATE DATABASE_DEFAULT;


EXECUTE dbo.InsertValues @T;