Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Posts
    37

    Unanswered: sql table datatype

    I need to pass a table selected in one sp to another stored procedure... i have posted in regards to this before however, I am not running sql server 2000 and the person I am developing this for is running sql server version 8. I have tried a bunch of things but ultimately what I'm looking to do is take the results set of select * from table, store it to a variable, and execute another stored procedure passing that variable along to the other sp... So here are my two procedures.... Can someone tell me if the reason that this won't work (error msg that I need to declare @table) is because I'm not running sql server 2000 or am I not passing the select statement to the parameter properly? If the reason has to do with the sql server 2000, could someone give me an example on how to pass the results of one table to another stored procedure?????

    CREATE PROCEDURE PROCEDURE1
    AS

    DECLARE @table TABLE (custlastname varchar(20), custfirstname varchar(20))

    BEGIN
    INSERT INTO @table
    Select custlastname, custfirstname
    from tblcustomer

    exec PROCEDURE2 @table
    GO

    CREATE PROCEDURE PROCEDURE2
    AS
    SELECT * from @table
    GO
    Last edited by justastef; 07-21-03 at 15:02.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    From Books Online
    All data types, except the table data type, can be used as a parameter for a stored procedure
    You could create a temporary table in PROCEDURE1 and then reference it in PROCEDURE2.
    MCDBA

  3. #3
    Join Date
    Jul 2003
    Posts
    37
    Okay so thats an awesome start! That works while on the same server... I have to move the result set across servers... so i have this

    CREATE PROCEDURE dbo.tabledatatype --on server MIDQ1
    AS
    --selecting all into temp table
    Select * INTO ##tabledatatype from tblcustomer
    --execute remote stored procedure
    exec MIDQ2.DSRBQ000.dbo.usp_tabledatatype


    CREATE PROCEDURE dbo.usp_tabledatatype --on server MIDQ2
    AS
    select * from ##tabledatatype


    Now obviously thats not going to work because the temp table is stored on the MIDQ1 server in tempdb.... So I tried this for usp_tabledatatype procedure for code and of course it didn't work:
    select * from MIDQ1.DSRBQ000.dbo.##tabledatatype

    Any idea how to select that temp table across the servers?

  4. #4
    Join Date
    Jul 2003
    Posts
    37

    Re: sql table datatype

    A lightening bolt has just hit my brain... and I've thought of a BETTER way to do this... so I'm gonna say forget this executing remote stored procedure thing... (since I'm attempting to do this across servers from server to server)... I'm just going to go directly to the second server and have only one procedure which performs a select statement on the first server/db and stores THAT into a temp table... so it will look something like this!!!

    CREATE PROCEDURE procedure1 AS
    SET ANSI_NULLS ON
    GO
    SET NOCOUNT OFF
    GO
    SET ANSI_WARNINGS ON
    GO

    SELECT * INTO #table FROM SERVER1.DB1.dbo.tblcustomer


    Its logical and it works with a basic table... It should work with a more complex select statement. Thank you guys for all your help and I'll be sure to post if I have any more questions!

    Originally posted by justastef
    I need to pass a table selected in one sp to another stored procedure... i have posted in regards to this before however, I am not running sql server 2000 and the person I am developing this for is running sql server version 8. I have tried a bunch of things but ultimately what I'm looking to do is take the results set of select * from table, store it to a variable, and execute another stored procedure passing that variable along to the other sp... So here are my two procedures.... Can someone tell me if the reason that this won't work (error msg that I need to declare @table) is because I'm not running sql server 2000 or am I not passing the select statement to the parameter properly? If the reason has to do with the sql server 2000, could someone give me an example on how to pass the results of one table to another stored procedure?????

    CREATE PROCEDURE PROCEDURE1
    AS

    DECLARE @table TABLE (custlastname varchar(20), custfirstname varchar(20))

    BEGIN
    INSERT INTO @table
    Select custlastname, custfirstname
    from tblcustomer

    exec PROCEDURE2 @table
    GO

    CREATE PROCEDURE PROCEDURE2
    AS
    SELECT * from @table
    GO

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Look at using XML. Select your data using FOR XML, then pass it to the second procedure, where they will decode the XML with sp_xml_preparedocument and SELECT..OPENXML. See Books Online or search GOOGLE for examples.
    MCDBA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •