A big issue when try to Alter user define table type structure TIP #108

 

I hope all of you aware of  User define table type (a table value parameter) which we discussed earlier in TIP #57.

Now recently one interesting incident happened. We are using a user define table type in few stored procedure and due to some business requirement change we need to change /update data type of a particular column from TINYINT to SMALLINT.

Now this change was seems very simple you just need to change a column’s data type  but when you are going to do this, you will find this is not pretty straight forward (if the User define table type is referred in different tables).

If you go through standard steps you need to follow below steps (for  a column data type change)

1) Create a new User define table  type

2) Replaced old User define table  type with new user define table type in each stored procedures

3) Remove old User define table type.

And , I would like to say a big thanks  Mr.Norlado  who post an alternative on stackoverflow

below is the alternative steps

1) Rename the existing table type with following command

EXEC sys.sp_rename ‘dbo.StudentTableType’, ‘zStudentTableType’;

2) Create Table type with your  changes which you want


CREATE TYPE dbo.StudentTableType AS TABLE(
    StudentId INT NOT NULL,
    Name VARCHAR(255) NOT NULL,
    ClassId SMALLINT — changed from tinyInt to smallint
);

3. Update the reference in sql entities

DECLARE @Name NVARCHAR(776);

DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + ‘.’ + referencing_entity_name
FROM sys.dm_sql_referencing_entities(‘dbo.StudentTableType’, ‘TYPE’);

OPEN REF_CURSOR;

FETCH NEXT FROM REF_CURSOR INTO @Name;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sys.sp_refreshsqlmodule @name = @Name;
    FETCH NEXT FROM REF_CURSOR INTO @Name;
END;

CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;

4. Now drop the renamed table type

DROP TYPE dbo.zStudentTableType;

This 4 steps helped me a lot.

I hope this may help you as well.

Enjoy!!!

RJ!!!

What is TVP (Table Value Parameter) & How to use it ? TIP #57

 

This is one of the interesting feature which I like most. Instead of passing values from collection one by one pass entire collection to stored procedure as a table value parameter.

I know above statement is not digestive enough so lets understand this by an example.

Suppose , I have a table tblStudent with 4 columns studentId, FirstName, LastName, and class  as shown in below figure

Studenttable

Now I need to insert value in this table so I have created a simple stored procedure as shown in below figure

without_tvpProc

Now , I can easily insert values into it by calling the stored procedure for each student FirstName, LastName, course,

Suppose , I need to insert 4 records in the table then I need to call this stored procedure 4 times as shown in below figure

call_sp_without_TVP

Now think you have .net program and you have to call this stored procedure more than 100 times Smile .

I know you are bit scary.

Not to worry thanks to SQL Server again by providing Table value parameter to handle such kind of situation.

Now let me explain how to create table value parameter and call it to resolve such problem step by step

Step 1:-

To create Table value parameter you have to  choose  user define data type  as shown in below figure

User_Define_Type

Step 2:-

Now you can modify the template script in my case I am creating a student type User defined table type as shown in below figure . You will find I am using all the column which I need to insert into tblStudent table

 

Student_Type_Definie

Step 3:-

Now I am writing a  new stored procedure which use this table type as shown in below figure

StudentType_Procedure

You find in the above snap we have created a procedure with student type parameter which we have created .

The point to remember here that the table value parameter should be READONLY when passes in a stored procedure

Step 5:- Now let see how to call this stored procedure which has student type table value parameter

PassingTVP_Sp_Call

Now when we run above snap statement we inserted 3 rows in tblStudent.

Now lets consider the above problem calling stored procedure 1000 times instead of that you will call stored procedure only once with this amazing Table value parameter feature.

You can pass any collection and data table as a parameter from .NET.

Isn’t it made our life easy ?  I am big fan of this feature.

I hope if you use you will also.

Hope this tip may help you somewhere.

Thanks & Enjoy !!!

RJ !!!