none
Use SP in SELECT

    Question

  • I have an INSERT stored procedure that I want to insert a dynamic value from another stored procedure as a column value.  Below is my sp that creates a new row followed by some code in a 2nd sp that uses it.  What is the correct syntax as I get "Incorrect syntax near '='". Thanks.

    ALTER PROCEDURE [dbo].[GLIDAdd]
     @SpecificDescriptionID int,
     @AuthorizedByID   int,
     @DateOfTransaction  datetime = null
       
    AS

     BEGIN
     
      SET NOCOUNT ON
      DECLARE @newGLID int;
      
      IF @DateOfTransaction IS NULL
       BEGIN
        --set to today date
        SET @DateOfTransaction = DATEADD(d,DATEDIFF(d,0,GETDATE()),0);
       END
      
      INSERT INTO [dbo].[GLID]
         ([SpecificDescriptionID]
         ,[AuthorizedByID]
         ,[DateOfTransaction]
         ,[time_stamp]
         )
      VALUES
         (@SpecificDescriptionID
         ,@AuthorizedByID
         ,@DateOfTransaction
         ,GETDATE()
         );
                
      SET @newGLID = SCOPE_IDENTITY();
      
      --send back newly created GLID
      SELECT @newGLID;
        
     END

     

       INSERT INTO [dbo].[Payable]
          ([IssuedGLID]
          ,[VendorAccountID]
          ,[ClientAccountID]
          ,[InvoiceNumber]
          ,[DueDate]
          ,[Amount]
          ,[Terms]
          ,[PurposeOfExpense]
          ,[Paid]
          ,[HoldCheck]
          )
       VALUES
          ((SELECT dbo.GLIDAdd(@SpecificDescriptionID=@SpecificDescriptionID, @AuthorizedByID=@AuthorizedByID, @DateOfTransaction=@DateOfTransaction)
          ,@VendorAccountID
          ,@ClientAccountID
          ,@InvoiceNumber
          ,@DueDate
          ,@Amount
          ,@Terms
          ,@PurposeOfExpense
          ,@Paid
          ,@HoldCheck
          );

    Monday, February 11, 2013 9:35 PM

Answers

  • There are a couple of ways to approach this which don't involve functions, namely using an OUTPUT variable, or from SQL 2005 onwards, you can use the OUTPUT clause.

    Work through this simple demo which shows both techniques and see either will work for you:

    USE tempdb
    GO
    
    IF OBJECT_ID('[dbo].[GLID]') IS NOT NULL DROP TABLE [dbo].[GLID]
    CREATE TABLE [dbo].[GLID] ( GLID INT IDENTITY PRIMARY KEY, [SpecificDescriptionID] INT, [AuthorizedByID] INT, [DateOfTransaction] DATETIME, [time_stamp] DATETIME )
    
    IF OBJECT_ID('[dbo].[Payable]') IS NOT NULL DROP TABLE [dbo].[Payable]
    CREATE TABLE [dbo].[Payable] ( [IssuedGLID] INT, [VendorAccountID] INT, [ClientAccountID] INT, [InvoiceNumber] INT, [DueDate] DATETIME, [Amount] MONEY, [Terms] INT, [PurposeOfExpense] INT, [Paid] INT, [HoldCheck] INT ) 
    GO
    
    
    ------------------------------------------------------------------------------------------------
    -- Method 1 START
    -- Use output variable
    ------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID('[dbo].[GLIDAdd]') IS NOT NULL DROP PROC [dbo].[GLIDAdd]
    GO
    CREATE PROCEDURE [dbo].[GLIDAdd]
      @SpecificDescriptionID int,
      @AuthorizedByID   int,
      @DateOfTransaction  datetime = null,
      @newGLID INT OUT
          
    AS 
    
    BEGIN
      
    	SET NOCOUNT ON 
       
    	IF @DateOfTransaction IS NULL
    	BEGIN
    	--set to today date
    	SET @DateOfTransaction = DATEADD(d,DATEDIFF(d,0,GETDATE()),0);
    	END
       
    	INSERT INTO [dbo].[GLID]
    	([SpecificDescriptionID]
    	,[AuthorizedByID]
    	,[DateOfTransaction]
    	,[time_stamp]
    	)
    	VALUES
    	(@SpecificDescriptionID
    	,@AuthorizedByID
    	,@DateOfTransaction
    	,GETDATE()
    	);
                  
    	SET @newGLID = SCOPE_IDENTITY();
       
       RETURN
    END
    GO
     
    
    -- Use @newGLID as output variable
    DECLARE @newGLID INT
    EXEC [dbo].[GLIDAdd]
    	@SpecificDescriptionID = 1,
    	@AuthorizedByID = 2,
    	@DateOfTransaction = NULL,
    	@newGLID = @newGLID OUT
    
    --SELECT @newGLID newGLID
    
    DECLARE 
    	@VendorAccountID INT = 10, @ClientAccountID INT = 20, @InvoiceNumber INT = 30, @DueDate DATETIME = GETDATE(), @Amount MONEY = RAND(), @Terms INT = 1, @PurposeOfExpense INT = 0, @Paid INT = 1, @HoldCheck INT = 0
    
    -- Use the output variable in your INSERT statement
    INSERT INTO [dbo].[Payable]([IssuedGLID], [VendorAccountID], [ClientAccountID], [InvoiceNumber], [DueDate], [Amount], [Terms], [PurposeOfExpense], [Paid], [HoldCheck] )
    VALUES ( @newGLID, @VendorAccountID, @ClientAccountID, @InvoiceNumber, @DueDate, @Amount, @Terms, @PurposeOfExpense, @Paid, @HoldCheck );
    
    
    SELECT 'GLID Table after first proc' s, * FROM [dbo].[GLID]
    SELECT 'Payable Table after first proc' s, * FROM [dbo].[Payable]
    GO
    
    -- Method 1 END
    ------------------------------------------------------------------------------------------------
    
    
    
    ------------------------------------------------------------------------------------------------
    -- Method 2 START
    -- From SQL 2005 onwards use output clause aka composable DML
    ------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID('[dbo].[GLIDAdd2]') IS NOT NULL DROP PROC [dbo].[GLIDAdd2]
    GO
    CREATE PROCEDURE [dbo].[GLIDAdd2]
    
      @SpecificDescriptionID int,
      @AuthorizedByID   int,
      @DateOfTransaction  datetime = null
          
    AS 
    
    BEGIN
      
    	SET NOCOUNT ON 
       
    	IF @DateOfTransaction IS NULL
    	BEGIN
    	--set to today date
    	SET @DateOfTransaction = DATEADD(d,DATEDIFF(d,0,GETDATE()),0);
    	END
       
    
    	DECLARE 
    		@VendorAccountID INT = 11, @ClientAccountID INT = 21, @InvoiceNumber INT = 31, @DueDate DATETIME = GETDATE(), @Amount MONEY = RAND(), @Terms INT = 1, @PurposeOfExpense INT = 0, @Paid INT = 1, @HoldCheck INT = 0
    
    
    	-- Use the output column in your INSERT statement
    	INSERT INTO [dbo].[Payable]([IssuedGLID], [VendorAccountID], [ClientAccountID], [InvoiceNumber], [DueDate], [Amount], [Terms], [PurposeOfExpense], [Paid], [HoldCheck] )
    	SELECT newGLID, @VendorAccountID, @ClientAccountID, @InvoiceNumber, @DueDate, @Amount, @Terms, @PurposeOfExpense, @Paid, @HoldCheck
    	FROM
    		(
    			INSERT INTO [dbo].[GLID]
    			([SpecificDescriptionID]
    			,[AuthorizedByID]
    			,[DateOfTransaction]
    			,[time_stamp]
    			)
    			OUTPUT inserted.GLID newGLID
    			VALUES
    			(@SpecificDescriptionID
    			,@AuthorizedByID
    			,@DateOfTransaction
    			,GETDATE()
    			)
    		) x
       
       RETURN
    END
    GO
    
    
    
    -- Use @newGLID as output variable
    EXEC [dbo].[GLIDAdd2]
    	@SpecificDescriptionID = 1,
    	@AuthorizedByID = 2,
    	@DateOfTransaction = NULL
    
    SELECT 'GLID Table after second proc' s, * FROM [dbo].[GLID]
    SELECT 'Payable Table after second proc' s, * FROM [dbo].[Payable]
    GO
    
    -- Method 2 END
    ------------------------------------------------------------------------------------------------

    I would also recommend reading Erland's excellent article on sharing data between stored procedures available from here:

    http://www.sommarskog.se/

    • Edited by wBob Tuesday, February 12, 2013 3:04 PM link provided
    • Proposed as answer by Naomi NModerator Tuesday, February 12, 2013 3:06 PM
    • Marked as answer by dlchase89 Tuesday, February 12, 2013 4:05 PM
    Tuesday, February 12, 2013 3:01 PM

All replies

  • You can't use a stored procedure in the body of a INSERT statement unless you follow the format:

    INSERT INTO #dlchase89 (col1, col2, col3)
    EXEC dbo.sproc;

    I don't know your table layout to say if this will or won't work, but you could do the insert from the procedure first then update the record to add values specified in local variables.


    Adam

    Monday, February 11, 2013 9:53 PM
  • I meant to get it from a table as I needed multiple inserts.  I changed the VALUES... section to below and it compiled cleanly and hope it works.

       SELECT dbo.GLIDAdd (@SpecificDescriptionID, @AuthorizedByID, @DateOfTransaction)
          ,VendorAccountID
          ,ClientAccountID
          ,@InvoiceNumber
          ,@DueDate
          ,MonthlyRent
          ,NULL
          ,@PurposeOfExpense
          ,0
          ,0
         FROM dbo.Lease
         WHERE ([SelectToPay] = 1);

    Monday, February 11, 2013 9:57 PM
  • When I run it with the SELECT I get the following error.

    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GLIDAdd", or the name is ambiguous.

    Is there another way to accomplish this?  The sp named dbo.GLIDAdd creates a new record in a table named GLID and the sp is suppose to return the new Identity value and place it in the [IssuedGLID] column of the Payable table.  I wanted to avoid having to use a CURSOR if possible.  Thanks.


    • Edited by dlchase89 Monday, February 11, 2013 11:07 PM
    Monday, February 11, 2013 10:50 PM
  • Looks like you will have to re-write sp named dbo.GLIDAdd as function to use it in the select statement.
    Monday, February 11, 2013 11:37 PM
  • How can I do that when functions do not allow an INSERT statement?  Thanks.
    Tuesday, February 12, 2013 1:08 PM
  • You can't.  You will need to restructure your code or use a different approach.  As Adam already indicated, you can use the insert...execute syntax to insert this resultset into a table and then use the table to do what you want. That should work with your existing approach but there are restrictions (which you can read about in BOL).  You could also change the procedure to return the ID of the inserted row in an output argument.   Lastly, your insert procedure is extremely trivial - refactoring in tsql to an extreme degree will only make life difficult, complex, fragile, and inefficient.  I'll also add that you might want to consider updating your schema since it appears that transaction date should be date and not datetime. 
    Tuesday, February 12, 2013 2:08 PM
  • You can write it as a scalar function (and then your current syntax would work), but performance will probably be bad.  You can try to write it as a table-valued function, which will have better performance, but will require a slight re-work of your query.  Something like:

    SELECT tvf.NameOfReturnColumn
          ,l.VendorAccountID
          ,l.ClientAccountID
          ,@InvoiceNumber
          ,@DueDate
          ,l.MonthlyRent
          ,NULL
          ,@PurposeOfExpense
          ,0
          ,0
    FROM dbo.Lease l
    Cross Apply dbo.GLIDAdd_TVF(@SpecificDescriptionID, @AuthorizedByID, @DateOfTransaction) tvf
    WHERE (l.[SelectToPay] = 1);

    Or, you have to execute the stored procedure and store the results in a temp table in the outer proc.  That would be something like:

    Create Procedure MyOuterProc (
    	@InputParam1 int
    	,@InputParam2 varchar(10)
    	,@OutputParam1 int OUTPUT
    	)
    As
    
    Create Table #TempTableForProc (
    	Column1 int
    	,Column2 varchar(10)
    	,ColumnWhatever char(1)
    	)
    
    Insert Into #TempTableForProc
    Exec MyOtherProcThatNeedsToBeCalled @Param, @Param, @Param
    
    SELECT t1.Column1
          ,l.VendorAccountID
          ,l.ClientAccountID
          ,@InvoiceNumber
          ,@DueDate
          ,l.MonthlyRent
          ,NULL
          ,@PurposeOfExpense
          ,0
          ,0
    FROM dbo.Lease l
    join #TempTableForProc t1
    	on	l.SomeMatchingColumn = t1.SomeMatchingColumn
    WHERE (l.[SelectToPay] = 1);

    Tuesday, February 12, 2013 2:12 PM
  • There are a couple of ways to approach this which don't involve functions, namely using an OUTPUT variable, or from SQL 2005 onwards, you can use the OUTPUT clause.

    Work through this simple demo which shows both techniques and see either will work for you:

    USE tempdb
    GO
    
    IF OBJECT_ID('[dbo].[GLID]') IS NOT NULL DROP TABLE [dbo].[GLID]
    CREATE TABLE [dbo].[GLID] ( GLID INT IDENTITY PRIMARY KEY, [SpecificDescriptionID] INT, [AuthorizedByID] INT, [DateOfTransaction] DATETIME, [time_stamp] DATETIME )
    
    IF OBJECT_ID('[dbo].[Payable]') IS NOT NULL DROP TABLE [dbo].[Payable]
    CREATE TABLE [dbo].[Payable] ( [IssuedGLID] INT, [VendorAccountID] INT, [ClientAccountID] INT, [InvoiceNumber] INT, [DueDate] DATETIME, [Amount] MONEY, [Terms] INT, [PurposeOfExpense] INT, [Paid] INT, [HoldCheck] INT ) 
    GO
    
    
    ------------------------------------------------------------------------------------------------
    -- Method 1 START
    -- Use output variable
    ------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID('[dbo].[GLIDAdd]') IS NOT NULL DROP PROC [dbo].[GLIDAdd]
    GO
    CREATE PROCEDURE [dbo].[GLIDAdd]
      @SpecificDescriptionID int,
      @AuthorizedByID   int,
      @DateOfTransaction  datetime = null,
      @newGLID INT OUT
          
    AS 
    
    BEGIN
      
    	SET NOCOUNT ON 
       
    	IF @DateOfTransaction IS NULL
    	BEGIN
    	--set to today date
    	SET @DateOfTransaction = DATEADD(d,DATEDIFF(d,0,GETDATE()),0);
    	END
       
    	INSERT INTO [dbo].[GLID]
    	([SpecificDescriptionID]
    	,[AuthorizedByID]
    	,[DateOfTransaction]
    	,[time_stamp]
    	)
    	VALUES
    	(@SpecificDescriptionID
    	,@AuthorizedByID
    	,@DateOfTransaction
    	,GETDATE()
    	);
                  
    	SET @newGLID = SCOPE_IDENTITY();
       
       RETURN
    END
    GO
     
    
    -- Use @newGLID as output variable
    DECLARE @newGLID INT
    EXEC [dbo].[GLIDAdd]
    	@SpecificDescriptionID = 1,
    	@AuthorizedByID = 2,
    	@DateOfTransaction = NULL,
    	@newGLID = @newGLID OUT
    
    --SELECT @newGLID newGLID
    
    DECLARE 
    	@VendorAccountID INT = 10, @ClientAccountID INT = 20, @InvoiceNumber INT = 30, @DueDate DATETIME = GETDATE(), @Amount MONEY = RAND(), @Terms INT = 1, @PurposeOfExpense INT = 0, @Paid INT = 1, @HoldCheck INT = 0
    
    -- Use the output variable in your INSERT statement
    INSERT INTO [dbo].[Payable]([IssuedGLID], [VendorAccountID], [ClientAccountID], [InvoiceNumber], [DueDate], [Amount], [Terms], [PurposeOfExpense], [Paid], [HoldCheck] )
    VALUES ( @newGLID, @VendorAccountID, @ClientAccountID, @InvoiceNumber, @DueDate, @Amount, @Terms, @PurposeOfExpense, @Paid, @HoldCheck );
    
    
    SELECT 'GLID Table after first proc' s, * FROM [dbo].[GLID]
    SELECT 'Payable Table after first proc' s, * FROM [dbo].[Payable]
    GO
    
    -- Method 1 END
    ------------------------------------------------------------------------------------------------
    
    
    
    ------------------------------------------------------------------------------------------------
    -- Method 2 START
    -- From SQL 2005 onwards use output clause aka composable DML
    ------------------------------------------------------------------------------------------------
    
    IF OBJECT_ID('[dbo].[GLIDAdd2]') IS NOT NULL DROP PROC [dbo].[GLIDAdd2]
    GO
    CREATE PROCEDURE [dbo].[GLIDAdd2]
    
      @SpecificDescriptionID int,
      @AuthorizedByID   int,
      @DateOfTransaction  datetime = null
          
    AS 
    
    BEGIN
      
    	SET NOCOUNT ON 
       
    	IF @DateOfTransaction IS NULL
    	BEGIN
    	--set to today date
    	SET @DateOfTransaction = DATEADD(d,DATEDIFF(d,0,GETDATE()),0);
    	END
       
    
    	DECLARE 
    		@VendorAccountID INT = 11, @ClientAccountID INT = 21, @InvoiceNumber INT = 31, @DueDate DATETIME = GETDATE(), @Amount MONEY = RAND(), @Terms INT = 1, @PurposeOfExpense INT = 0, @Paid INT = 1, @HoldCheck INT = 0
    
    
    	-- Use the output column in your INSERT statement
    	INSERT INTO [dbo].[Payable]([IssuedGLID], [VendorAccountID], [ClientAccountID], [InvoiceNumber], [DueDate], [Amount], [Terms], [PurposeOfExpense], [Paid], [HoldCheck] )
    	SELECT newGLID, @VendorAccountID, @ClientAccountID, @InvoiceNumber, @DueDate, @Amount, @Terms, @PurposeOfExpense, @Paid, @HoldCheck
    	FROM
    		(
    			INSERT INTO [dbo].[GLID]
    			([SpecificDescriptionID]
    			,[AuthorizedByID]
    			,[DateOfTransaction]
    			,[time_stamp]
    			)
    			OUTPUT inserted.GLID newGLID
    			VALUES
    			(@SpecificDescriptionID
    			,@AuthorizedByID
    			,@DateOfTransaction
    			,GETDATE()
    			)
    		) x
       
       RETURN
    END
    GO
    
    
    
    -- Use @newGLID as output variable
    EXEC [dbo].[GLIDAdd2]
    	@SpecificDescriptionID = 1,
    	@AuthorizedByID = 2,
    	@DateOfTransaction = NULL
    
    SELECT 'GLID Table after second proc' s, * FROM [dbo].[GLID]
    SELECT 'Payable Table after second proc' s, * FROM [dbo].[Payable]
    GO
    
    -- Method 2 END
    ------------------------------------------------------------------------------------------------

    I would also recommend reading Erland's excellent article on sharing data between stored procedures available from here:

    http://www.sommarskog.se/

    • Edited by wBob Tuesday, February 12, 2013 3:04 PM link provided
    • Proposed as answer by Naomi NModerator Tuesday, February 12, 2013 3:06 PM
    • Marked as answer by dlchase89 Tuesday, February 12, 2013 4:05 PM
    Tuesday, February 12, 2013 3:01 PM