February 5, 2015 at 9:29 am
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
February 5, 2015 at 10:24 am
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
February 5, 2015 at 10:36 am
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. ThanksCREATE 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
February 5, 2015 at 11:00 am
Thanks Igor for your valuable suggestions.
February 6, 2015 at 2:26 am
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 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
February 6, 2015 at 4:53 pm
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