Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can table-valued parameter be null? Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 11:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 3:31 AM
Points: 18, Visits: 73
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,
Post #1521746
Posted Tuesday, December 10, 2013 11:58 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 752, Visits: 1,324
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.
Post #1521752
Posted Wednesday, December 11, 2013 12:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:51 AM
Points: 1,040, Visits: 3,106
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
Post #1521756
Posted Wednesday, December 11, 2013 2:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 6, 2014 3:31 AM
Points: 18, Visits: 73
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
Post #1521779
Posted Wednesday, December 11, 2013 3:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:52 AM
Points: 752, Visits: 1,324
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.
Post #1521804
Posted Wednesday, December 11, 2013 5:30 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 31, 2014 5:09 AM
Points: 45, Visits: 139
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.


Post #1521848
Posted Wednesday, December 11, 2013 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:32 PM
Points: 3,420, Visits: 5,351
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1522111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse