saravanakumar.G (12/11/2013)
Hi,You just modify the SP like below and it should work.
CREATE PROCEDURE spNewEmployee@FirstName varchar(50),
@LastName varchar(50),
@skills SkillsTableType READONLY
AS
BEGIN
DECLARE @EmployeeID int
INSERT INTO Employees
(FirstName, LastName)
VALUES
(@FirstName, @LastName)
SET @EmployeeID = SCOPE_IDENTITY()
insert into Employeeskill(EmployeeID,SkillID , SkillLevelID)
SELECT @EmployeeID,SkillID , SkillLevelID from @skills
END
In this mode, It will insert into Employeeskill table if record exist in table values parameter. If no record exist then it will not insert any record. You dont have to specify the default value for the table-valued parameter.
I can certainly understand why this question comes up because it appears BOL on CREATE PROCEDURE is unclear on this usage.
Most parameters to a SP are required unless a default value is set with = in the parameters list. In the case of a TVP, you cannot set a default value but you don't have to. SQL doesn't complain if you execute the SP without specifying a value for that parameter.
And as Mr. saravanakumar.G has suggested, when you SELECT from it, no rows are returned.
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