none
Passing table variable to a stored procedure

    Question

  • Hi All,

    Firstly, is it possible to call a stored procedure from within a Select statement?

    Secondly, I want to pass several TABLE parameters to a stored procedure which returns me an output parameter. If I perform a select statement (hence the question above) i get the parameters i need to send to the stored procedure, but HOW DO I DO IT?

    Info: I have a customer table which i want to pass the customers street, town, county, postalcode, country to a store procedure (which will then insert the passed parameters into a seperarte Address table). I then want the store procedure to pass me the AddressID.

    Code Snippet:

    Store Procedure -

    ALTER PROCEDURE [dbo].[GetAddress]
    @AddressID        INT        OUTPUT,
    @Address        NVARCHAR(300),
    @Street            NVARCHAR(180),
    @Town            NVARCHAR(35),
    @County            NVARCHAR(35),
    @PostalCode        NVARCHAR(10),
    @Country        NVARCHAR(35)
    AS
    INSERT INTO Addresses
    (Address, Street, Town, County, PostalCode, Country)
    VALUES (@Address, @Street, @Town, @County, @PostalCode, @Country)
    SELECT
        @AddressID = (SELECT MAX(AddressID) FROM Addresses)

    Code -

    DECLARE @AddressID        INT,
            @RowCount        INT,
            @MaxRows        INT,
    --        @ExecSql        NVARCHAR(300),
    --        @Proc            NVARCHAR(50)

    SELECT @RowCount = 1
    --SELECT @Proc = 'GetAddress'
    SELECT @MaxRows = COUNT(*) FROM Customer WHERE CustomerID <= 10

    WHILE @RowCount <= @MaxRows
    BEGIN
    --    SELECT @ExecSql = 'Exec ' + @Proc + ' ''' + @AddressID + ''', ''' + Address + ''', ''' + Street + ''', ''' + Town + ''', ''' + County + ''', ''' + PostalCode + ''', ''' + Country + '''' FROM Customer WHERE CustomerID = @RowCount
        SELECT Address, Street, Town, County, PostalCode, Country FROM Customer WHERE CustomerID = @RowCount
        EXEC GetAddress @AddressID OUTPUT, Address, Street, Town, County, PostalCode, Country
    --    EXEC sp_executesql @ExecSql
        SELECT @RowCount = @RowCount + 1
    END

     

    As you can see from my code above i have tried several scenarios, the commented out code works ok BUT it doesn't return an output parameter and i'm not sure if i can get it to (any suggestions).

    I hope someone can help me out :)

    Tuesday, October 25, 2011 11:23 AM

Answers

  • Firstly, is it possible to call a stored procedure from within a Select statement?

    No.

    Before we go any further, let me note one thing about your procedure:

    SELECT    @AddressID = (SELECT MAX(AddressID) FROM Addresses)

    I assume that AddressID is an IDENTITY column. The correct way to get the ID for the inserted row is:

    SELECT @AddressID = scope_identity()

    Using MAX means that you can get the row someone else inserted after you - or, actually, yesterday or three weeks if the identity value was reseeded.

    Secondly, I want to pass several TABLE parameters to a stored procedure which returns me an output parameter. If I perform a select statement (hence the question above) i get the parameters i need to send to the stored procedure, but HOW DO I DO IT?

    The best approach is to simply insert all rows in a singe INSERT statement and use the OUTPUT clause to get the IDENTITY values. There is not much meat in that procedure warrant reuse.

    But if you want to run it one by one - and you know that this is defendable from a performance perspective, you would do it like this:

    DECLARE cur CURSOR STATIC LOCAL FOR
         SELECT Address, Street, Town, Country, PostalCode, Country
         FROM     Customers
         WHERE    CustomerID <= 10
    
    OPEN cur
    
    WHILE 1 = 1
    BEGIN
         FETCH cur INTO @Address, @Street, @Town, @Country, @PostalCode, @Country
         IF @@fetch_status <> 0
                BREAK
    
         EXEC GetAddress @AddressID OUTPUT,
                                         @Address, @Street, @Town, @Country, @PostalCode, @Country
    END
    
    DEALLOCATE cur

    This code:

    --    SELECT @ExecSql = 'Exec ' + @Proc + ' ''' + @AddressID + ''', ''' + Address + ''', ''' + Street + ''', ''' + Town + ''', ''' + County + ''', ''' + PostalCode + ''', ''' + Country + '''' FROM Customer WHERE CustomerID = @RowCount
        SELECT Address, Street, Town, County, PostalCode, Country FROM Customer WHERE CustomerID = @RowCount
    --    EXEC sp_executesql @ExecSql

    has several problems. You don't get the addressId because you are not specifying the OUTPUT flag for the output parameter, nor are you receiving this parameter from dynamic SQL.

    Now, there is zero reason to use dynamic SQL in this case, but because you don't seem to master dynamic SQL, I like to show you how the call should look like:

    EXEC sp_executesql N'EXEC GetAddress @AddressID OUTPUT,
                                     @Address, @Street, @Town, @Country, @PostalCode, @Country',
                                 N'@AddressID        INT        OUTPUT,
                                     @Address        NVARCHAR(300),
                                     @Street            NVARCHAR(180),
                                     @Town            NVARCHAR(35),
                                     @County            NVARCHAR(35),
                                     @PostalCode        NVARCHAR(10),
                                     @Country        NVARCHAR(35)',
         @AddressID OUTPUT,
                @Address, @Street, @Town, @Country, @PostalCode, @Country

    That is, first you pass the statement, which shold be parameterised. Next you pass the parameter list, which looks just like a parameter list to a stored procedure. And finally you pass the parameters.

    If you try to build a complete SQL string from values, there is no end of the misery you can end up in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Tuesday, October 25, 2011 3:54 PM
    • Marked as answer by KJian_ Tuesday, November 01, 2011 8:15 AM
    Tuesday, October 25, 2011 1:15 PM

All replies

  •  TO return a value from your stored procedure you need to add a return value. " Return @AddressID"

    You dynamic SQL to populate the the variables you want to pass to the procedure , then you can execute the dynamic SQL passing the values and get your return value.

    No you cannot call a stored proc from within a Select statement.

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Tuesday, October 25, 2011 11:32 AM
  • http://sqlblog.com/blogs/rob_farley/archive/2011/10/20/table-valued-parameters-in-sql-2005.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 25, 2011 11:34 AM
  • http://sqlblog.com/blogs/rob_farley/archive/2011/10/20/table-valued-parameters-in-sql-2005.aspx
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 25, 2011 11:34 AM
  • Are you on SQL 2008 or later? If so, I suggest you switch to using table-valued parameters:

     

    http://msdn.microsoft.com/en-us/library/bb510489.aspx

    this will enable you to avoid using any dynamic SQL.

    Ben

    Tuesday, October 25, 2011 12:47 PM
  • Firstly, is it possible to call a stored procedure from within a Select statement?

    No.

    Before we go any further, let me note one thing about your procedure:

    SELECT    @AddressID = (SELECT MAX(AddressID) FROM Addresses)

    I assume that AddressID is an IDENTITY column. The correct way to get the ID for the inserted row is:

    SELECT @AddressID = scope_identity()

    Using MAX means that you can get the row someone else inserted after you - or, actually, yesterday or three weeks if the identity value was reseeded.

    Secondly, I want to pass several TABLE parameters to a stored procedure which returns me an output parameter. If I perform a select statement (hence the question above) i get the parameters i need to send to the stored procedure, but HOW DO I DO IT?

    The best approach is to simply insert all rows in a singe INSERT statement and use the OUTPUT clause to get the IDENTITY values. There is not much meat in that procedure warrant reuse.

    But if you want to run it one by one - and you know that this is defendable from a performance perspective, you would do it like this:

    DECLARE cur CURSOR STATIC LOCAL FOR
         SELECT Address, Street, Town, Country, PostalCode, Country
         FROM     Customers
         WHERE    CustomerID <= 10
    
    OPEN cur
    
    WHILE 1 = 1
    BEGIN
         FETCH cur INTO @Address, @Street, @Town, @Country, @PostalCode, @Country
         IF @@fetch_status <> 0
                BREAK
    
         EXEC GetAddress @AddressID OUTPUT,
                                         @Address, @Street, @Town, @Country, @PostalCode, @Country
    END
    
    DEALLOCATE cur

    This code:

    --    SELECT @ExecSql = 'Exec ' + @Proc + ' ''' + @AddressID + ''', ''' + Address + ''', ''' + Street + ''', ''' + Town + ''', ''' + County + ''', ''' + PostalCode + ''', ''' + Country + '''' FROM Customer WHERE CustomerID = @RowCount
        SELECT Address, Street, Town, County, PostalCode, Country FROM Customer WHERE CustomerID = @RowCount
    --    EXEC sp_executesql @ExecSql

    has several problems. You don't get the addressId because you are not specifying the OUTPUT flag for the output parameter, nor are you receiving this parameter from dynamic SQL.

    Now, there is zero reason to use dynamic SQL in this case, but because you don't seem to master dynamic SQL, I like to show you how the call should look like:

    EXEC sp_executesql N'EXEC GetAddress @AddressID OUTPUT,
                                     @Address, @Street, @Town, @Country, @PostalCode, @Country',
                                 N'@AddressID        INT        OUTPUT,
                                     @Address        NVARCHAR(300),
                                     @Street            NVARCHAR(180),
                                     @Town            NVARCHAR(35),
                                     @County            NVARCHAR(35),
                                     @PostalCode        NVARCHAR(10),
                                     @Country        NVARCHAR(35)',
         @AddressID OUTPUT,
                @Address, @Street, @Town, @Country, @PostalCode, @Country

    That is, first you pass the statement, which shold be parameterised. Next you pass the parameter list, which looks just like a parameter list to a stored procedure. And finally you pass the parameters.

    If you try to build a complete SQL string from values, there is no end of the misery you can end up in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi NModerator Tuesday, October 25, 2011 3:54 PM
    • Marked as answer by KJian_ Tuesday, November 01, 2011 8:15 AM
    Tuesday, October 25, 2011 1:15 PM
  • Why did you not post DDL? 

    Why not use a VIEW? I know that punch card programmers always think about extracting data from a magnetic tape to a deck of cards for their task at hand; we SQL people do not. I see you also have a  counter, just the sequence on a punch card machine. 

    ISO Country codes are CHAR(3), but you want to use 35 Chinese Unicode. The rest of your address data is clearly not researched. Is "county" the name, population or a census code?  You only have ONE customer? That is what your table name says to the world. Why invite garbage data? 

    Dynamic SQL is a sign of failure. You are telling the world that your specs and schema are so bad that you have to fix them in the front end at the last minute. 

    In many industries, there is a SAN (Standard Address Number) ; if you do not have one, then use FedEx, DHL or UPC codes. My guess, based on your DDL is that you coudl use a deried table:

     

    (SELECT cust_name, street_addr, town_name, county_name, postal_code, ountry_code 

      FROM Customers --- more than one! 

     WHERE cust_nbr BETWEEN @start_cust_nbr AND @end_cust_nbr)

    AS Cust_Addresses


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Tuesday, October 25, 2011 3:51 PM