December 10, 2013 at 11:32 pm
Hii Guys,
I'm new to table valued parameters -- admittedly a little late but better late than never.
I'm trying to insert some "new" employees into my Employees table. However, if the new employee has some predetermined skills, I want to enter them during the insert for the employee. The curve ball here is that the new employee may not have any predetermined skills in which case the table-valued parameter would have to be null.
Can I actually set it to null? When I tried it, it gave me an error where I'd usually would "= null". So the idea is something like this:
CREATE PROCEDURE spNewEmployee
@FirstName varchar(50),
@LastName varchar(50),
@skills SkillsTableType READONLY = null
AS
BEGIN
DECLARE @EmployeeID int
INSERT INTO Employees
(FirstName, LastName)
VALUES
(@FirstName, @LastName)
SET @EmployeeID = SCOPE_IDENTITY()
-- This is where I'd like to check to see if @skills is NULL
-- And if not, I'd like to make entries into EmployeeSkills table using the @EmployeeID I just got and the data coming from @skills
END
The @skills parameter would simply have the SkillID and SkillLevelID.
Could someone tell me if a table-valued parameter can be null? If so, how do I actually use it in this instance where I'm getting the EmployeeID from SCOPE_IDENTITY and using it w/ the table-valued parameter received?
Not to mention, if @skills is null, then I only insert the new employee and not worry about adding the skills to his/her profile.
Thanks,
December 10, 2013 at 11:58 pm
As the User Define table Types are created as table-valued, so you cannot assign null to a table.
2ndly if you want to check if there is any value in the variable then you can use the following:
Place the following code after the scope_identity()
IF EXISTS ( Select 1 from @skills)
Begin
-- Add your code here
End
Hope it helps.
December 11, 2013 at 12:04 am
twin.devil (12/10/2013)
As the User Define table Types are created as table-valued, so you cannot assign null to a table.2ndly if you want to check if there is any value in the variable then you can use the following:
Place the following code after the scope_identity()
IF EXISTS ( Select 1 from @skills)
Begin
-- Add your code here
End
Hope it helps.
I would like to check this for you, but I can't at the moment, but my gut feel is the same as twin.devil's. Basically you can't have a null table, but you can have an empty table
December 11, 2013 at 2:19 am
No guys my Requirement is to make it null not to check whether it is null.
From the front end i will pass the values but at times i may not send the values even though my Stored Procedure should accept the given parameters instead of arising the errors like expected input parameter @skills.
my requirement is some thing as follows
@skills SkillsTableType READONLY = null
Thanks
December 11, 2013 at 3:48 am
Trainee SQL (12/11/2013)
No guys my Requirement is to make it null not to check whether it is null.From the front end i will pass the values but at times i may not send the values even though my Stored Procedure should accept the given parameters instead of arising the errors like expected input parameter @skills.
my requirement is some thing as follows
@skills SkillsTableType READONLY = null
Thanks
Here is an example for you. it might help you understand the working of user define table type
CREATE TYPE [dbo].[udtSample] AS TABLE(
[id] [int] NULL,
[Name] [varchar](20) NULL
)
GO
------------------------------
Create Procedure uspSample
@sample as [dbo].[udtSample] READONLY
AS
Select *
from @sample;
GO
exec uspSample
GO
this code will run without any issue. i hope it will answer your question.
December 11, 2013 at 5:30 am
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.
December 11, 2013 at 6:02 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply