Introduction:
In this article will see how we can pass table type as input to a Stored Procedure (SP) in SQL server with an example and unit the SP with table type. Let’s begin.
Basic Requirements:
One who wants to understand this article – needs to know what is a SP, creating Types in SQL server.
Example overview:
In this example will create simple SP that validates username and password and returns a user exists or not (bit). We can do this by passing parameters username an password as input to SP, but here we interested to learn table type passing to SP, so I’m creating a table type with username and password and this table type I’m passing it as input for SP.
To unit test this we will create dummy data with table type and will pass to SP with for positive and negative scenarios. This simple example will make us to understand the passing a table type to SP.
Step 1: Creating a Table Type:
Before jumping actual code – I created a Database with name ‘DevWorks’. I have created a dbo.AppUser table with columns AppUserPID (Primary Key), Username and Password. For demo purpose, I have created two records for users in Appuser Table.
Open a Microsoft SQL Server Management Studio, here we need to create a table type with name ‘AuthenticateUserType’ which as username and password has two columns.
/*Table Type for holding username and password fields.*/
CREATE TYPE AuthenticateUserType as Table
( UserName varchar(50),
[Password] varchar(50)
)
GO
Step 2: Create SP to validate user
Now, Create SP to validate user exists in our database by using simple select query, if user exist just return true else false from SP. In real time after validating user – will return session ID. But in this example will keep it simple.
In this SP I used join condition with incoming data and checking whether user exists or not, depending on the same, SP will return a true or false conditions as output parameter.
/*SP to validate user which takes table type*/
CREATE PROCEDURE dbo.SP_ValidateUser
@UserData AuthenticateUserType readonly,
@IsValidUser BIT OUT
AS
BEGIN
Declare @userExists bit
IF EXISTS(SELECT 0 FROM dbo.AppUser au
INNER JOIN @UserData ud
ON au.UserName = ud.UserName
AND au.Password = ud.Password
)
BEGIN
SET @userExists = 1
END
ELSE
BEGIN
SET @userExists = 0
END
SET @IsValidUser = @userExists
Return @IsValidUser
END
GO
Step 4: Unit testing the SP and Table Type
To unit this SP which takes table type as input, first we need to create a table variable using same table type and for unit testing purpose we want to insert the data into the same.
To unit test for positive scenario – To unit the positive scenario – we will insert the data into the table variable that actually exists in Appuser table.
To unit test for negative scenario – To unit the positive scenario – we will insert the data into the table variable that not exists in Appuser table (random data).
Finally, Pass this table type to SP to validate user – output variable will decide the whether user valid or invalid.
/*Unit Testing*/
Declare @data as AuthenticateUserType
INSERT INTO @data (username,password)
Values(‘Deo’,’%^**’)
SELECT * FROM @data
DECLARE @IsUserValid bit
EXEC SP_validateUser @data, @isUserValid out
SELECT @IsUserValid
Image – Unit test – SP result.
Conclusion: In this article we seen simple example of creating table type and passing it to the SP, real time of using table type like in notification servers SP’s – to pass huge data of users to notify like in all social medias. Thanks.
Happy Coding 🙂