Table Type and OpenRowSet

  • I'm trying to store the output of my stored procedure into a temp table, which takes Table Type as Input.

    CREATE TYPE [dbo].[Employee] AS TABLE(

    [Field] [varchar](50) NULL,

    [Criteria] [varchar](50) NULL,

    [Value] [varchar](50) NULL

    )

    declare @Employee dbo.Employee

    insert into @Employee values(N'Salary',N'is greater than',N'3520')

    DECLARE @sql NVARCHAR(MAX)

    SET @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'',''Server=localhost;Trusted_Connection=yes;'',''EXEC dbo.uspGetEmployee ' + @Employee + ')'

    I need to create a stored procedure which uses output of the above stored procedure. Hence, I don't want to put declare and insert into OPENROWSET as I get those values as parameter to the new procedure.

    It would be great if you have any other solution instead of using OpenRowSet.

    Can anyone please advise on how to construct dynamical sql here? Thanks

  • I have created a table type matching my output of stored procedure and am able to store the data. I'm still interested to see if there will be any options other than creating table type. Thanks

    CREATE TYPE [dbo].[Employee] AS TABLE(

    [Field] [varchar](50) NULL,

    [Criteria] [varchar](50) NULL,

    [Value] [varchar](50) NULL

    )

    declare @Employee dbo.Employee

    insert into @Employee values(N'Salary',N'is greater than',N'3520')

    CREATE TYPE dbo.SearchResult AS TABLE

    (

    Name VARCHAR(50)

    DateOfBirth DATETIME

    )

    DECLARE @Result dbo.SearchResult

    INSERT INTO @Result

    EXEC dbo.uspGetEmployee @Employee

  • sarath.tata (2/5/2015)


    I have created a table type matching my output of stored procedure and am able to store the data. I'm still interested to see if there will be any options other than creating table type. Thanks

    CREATE TYPE [dbo].[Employee] AS TABLE(

    [Field] [varchar](50) NULL,

    [Criteria] [varchar](50) NULL,

    [Value] [varchar](50) NULL

    )

    declare @Employee dbo.Employee

    insert into @Employee values(N'Salary',N'is greater than',N'3520')

    CREATE TYPE dbo.SearchResult AS TABLE

    (

    Name VARCHAR(50)

    DateOfBirth DATETIME

    )

    DECLARE @Result dbo.SearchResult

    INSERT INTO @Result

    EXEC dbo.uspGetEmployee @Employee

    TVPs are really helpful when you need to pass a table as a variable in a stored procedure, but be aware that TVPs without clustered index perform worse. Put a clustered index on it and you will have smoother performance.

    Second (as optional), an experience I faced up with TVPs recently is that when it accepts several hundreds of rows, or more, and additionally it has some other columns (like yours) then putting the content of the TVP into a #temp table in the beginning of the stored procedure and then using the #temp table brings benefit in terms of the statistics being present at temporary tables.

    HTH

    Igor Micev,My blog: www.igormicev.com

  • Thanks Igor for your valuable suggestions.

  • Out of curiosity, is the SP equipped to handle the case where there are multiple rows in the @employee table you pass into it?

    Normally I would have used separate parameters to the SP for each of the columns in the @employee table.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Yes, it is capable of handling multiple rows.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply