How to pass table as a parameter to SP?

In earlier versions of SQL SERVER before SQL SERVER 2008, it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

Steps to pass table value parameter to stored procedure

1. Create a table type and define the table structure
2. Declare a stored procedure that has a parameter of table type.
3. Declare a table type variable and reference the table type.
4. Using the INSERT statement and occupy the variable.
5. We can now pass the variable to the procedure.

Let’s take an example to see how pass table variable as a parameter to stored procedure

First create a table type and define columns in it

create type myTableType as table

(id int not null primary key,

name varchar(12))

Command(s) completed successfully.

Second, Create a stored procedure which will be taking a parameter of type Table ie myTableType

create procedure myProc

@tableVariable myTableType readonly

as

begin

      select * from @tableVariable

end  

Command(s) completed successfully.

Now lets declare a variable of type Table ie myTableType and insert few records in it and execute our procedure by passing the table varible

declare @tableParameter myTableType

insert into @tableParameter values(1,'Roshna')

insert into @tableParameter values(2,'Sanjana')

      

--lets execute the sp by passing table variable as a parameter

exec myProc @tableParameter 

(1 row(s) affected)

id          name

----------- ------------

1           Roshna

2           Sanjana

(2 row(s) affected)

With table value parameter we can pass complete datable from front end application to the stored procedure, instead of one by one row. Hopefully this solution in SQL Server 2008 will get rid of all the now unnecessary “hacks” that used XML, comma separated lists.

Code it and unwind self  

Post Reference: Vikram Aristocratic Elfin Share

How to Add a NOT NULL column in an existing table? Is it possible, seems not!!! What’s your reaction? Let’s try out

Let stimulate the whole scenario, by taking a table NotNullColumnDemo with two column firstName and lastName

create table NotNullColumnDemo

( firstName varchar(10),

  lastName varchar(10))

Command(s) completed successfully.

Lets insert two row in the table :

insert into NotNullColumnDemo values ('Mythri','Patel')

insert into NotNullColumnDemo values ('Mansha','Patnaik')

(1 row(s) affected)

(1 row(s) affected)

select * from NotNullColumnDemo

firstName  lastName

---------- ----------

Mythri     Patel

Mansha     Patnaik

(2 row(s) affected)

Now let’s try to add a column salary with a NOT NULL constraint as what our requirement is

alter table NotNullColumnDemo

add salary numeric(7,0) not null

OUTPUT

Msg 4901, Level 16, State 1, Line 1

ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'salary' cannot be added to non-empty table 'NotNullColumnDemo' because it does not satisfy these conditions.

Opps!!! Why it is not allowing me to add a new column with NOT NULL constraint???

Reason: Simply, you can't. Because if you could SQL wouldn't know what to put as value in the already existing records. If you don't have any records in the table it would work by the way.

So What to do now, because Manager needs no justification, he want me to do, cant to done work??

Crook Solution: The best way to do this is create the column with a default and then remove the default constraint. Lets fuel conceptual solution through t-sql

alter table NotNullColumnDemo

add salary numeric(7,0) not null

constraint df_salaray default 49000

Command(s) completed successfully.

select * from NotNullColumnDemo

firstName  lastName   salary

---------- ---------- ---------------------------------------

Mythri     Patel      49000

Mansha     Patnaik    49000

(2 row(s) affected)

alter table NotNullColumnDemo

drop constraint df_salaray

Command(s) completed successfully.

Now you have a column salary with NOT NULL constraint, you can now update the existing record salary column with the correct salary amount.  

Coding is cool, let’s take pleasure in it                 

Post Reference: Vikram Aristocratic Elfin Share

Inserting data into a table that has just one column which is identity type???

If you have a table with one column, which is promoted with an identity column and you were told to insert rows into the table, how you will be performing the command?

Let’s stimulate the case by taking a table OneColumnTable with only one column id

create table OneColumnTable

(id int identity(1,1))

Now try to insert record in this table using insert statement

insert into OneColumnTable values(1)

//OUTPUT

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'OneColumnTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So it is not allowing to insert record in the table, but then you got a task to insert data into this table, how to do the insertion, lets fire the below query

insert into OneColumnTable default values

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

(1 row(s) affected

Note: If you want to continue inserting identity values, then you can make use of default value clause

And, If you want to insert explicit values then you can write

set identity_insert oneColumnTable on

insert into OneColumnTable(id) values(5);

set identity_insert oneColumnTable on

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

5

(2 row(s) affected)

That’s how you can insert values in identity column. J

Put out of your mind, it’s coding; very straightforward J   

Post Reference: Vikram Aristocratic Elfin Share

How to convert 0 to 1 and 1 to 0 in table object

Say I am having a table ZeroOneZero with following data

select * from ZeroOneZero

name       Active

---------- ------

Prakriti   0

Aaliya     1

Falak      0

Snehal     1

(4 row(s) affected)

Now what I want is to convert 0 values in Active column to 1 and vice versa. It seems very easy to do, yes absolutely it is, but you need make sure you don’t have to use where clause in Update statement else you will end up getting something unexpected….

Let’s check out how to get done with this problem, the Case clause is the solution:

update ZeroOneZero

set Active =

      (case when Active = 0 then 1

              when Active = 1 then 0

      end)

(4 row(s) affected)

/*Now lets fire select query to see the result*/

select * from ZeroOneZero

name       Active

---------- ------

Prakriti   1

Aaliya     0

Falak      1

Snehal     0

(4 row(s) affected)

Yes we are through.

Coding a Psyche Game

Post Reference: Vikram Aristocratic Elfin Share

Multiple Value Inserts Using a Single INSERT Statement

With the introduction of row constructor in SQL 2008, you can now insert multiple values using a single INSERT statement.

With the row constructor, you can now insert multiple values using a single INSERT statement. In SQL Server 2000 or SQL Server 2005, to insert multiple rows to a table, you would have need of one INSERT statement for each record.   To exemplify, to insert the multiple records to EmpTest Table, you would do something like the following

insert into EmpTest values('sakshi',2)

insert into EmpTest values('Aishwarya',3)

insert into EmpTest values('Arti',2)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

The individual insert into... values statement can now be cut down with the row constructor.  The script above will now look as follows:

insert into EmpTest values('Vibha',1),

                        ('Sandhya',3),

                        ('Aurpita',1)

(3 row(s) affected)

SELECT with VALUES Clauses in the INSERT Statement Row Constructor

It allow a combination of specifying a list of values using the VALUES clause and getting data from another table using the SELECT statement

Note: Subqueries are allowed as long as the subquery returns only a single column and a single row

insert into EmpTest values('Paavai',2),

                          ('Edwina',3),

((select o.name from OneRowTable o where o.id =1),

                          (select '3'))

select * from EmpTest

emp_id      emp_name   dept_id

----------- ---------- -----------

1           sakshi     2

2           Aishwarya  3

3           Arti       2

4           Vibha      1

5           Sandhya    3

6           Aurpita    1

7           Paavai     2

8           Edwina     3

9           vikram     3

(9 row(s) affected)

Enclose grand code

Post Reference: Vikram Aristocratic Elfin Share

Ensure there is one row in a Table

How to ensure there is only one row in a table

There are situation where you want your table to contain only one row, generally such tables are used to store some configuration information, and you want :

• No one even accidently can delete the rows in it
• No one can enter new record to that table

If such is your requisite then we can achieve your requirement by implementing “Instead Of Triggers” on Table, let see how we are implementing our solution:

We will simulate the whole problem by taking example; we create a table named OneRowTable then insert single record in it:

create table OneRowTable

(id int identity(1,1),

name varchar(10) )

Command(s) completed successfully.

insert into OneRowTable values('Siddhi')

(1 row(s) affected)

Now we try to create Insert Preventive Trigger on OneRowTable, so that when anyone try to insert new record, it will not allow to insert and prompt a message.

create trigger  tr_insert_prevent

ON OneRowTable INSTEAD OF INSERT

AS

begin

declare @cnt int

      select @cnt=count(*) from OneRowTable

      if @cnt > 0

      begin

            raiserror('The table must always have only one row',1,1)                  

      end

      else

      begin

            insert into OneRowTable

            select i.name from inserted i

      end  

end

GO

Command(s) completed successfully.

Insertion after Trigger

Now since we have created this trigger which prevent user to enter new row to the table, it should give error message while someone tries to insert new record. Let’s check out

   

insert into OneRowTable values('Kyasti')

The table must always have only one row.

Msg 50000, Level 1, State 1

(1 row(s) affected)

We can see as we go for inserting new row, trigger cam into action and prevent the inserting of record.

Deletion after Trigger

Since we have Trigger in place, it will restrict the user from deleting rows from OneRowTable, let’s have a look to it

delete OneRowTable

The table must always have only one row.

Msg 50000, Level 1, State 1

(1 row(s) affected)

So you can see it prevent me from deleting record.

Coding is what coders know “BLISS”

Post Reference: Vikram Aristocratic Elfin Share

Varchar(N) VS Varchar(Max) from deeper view... Series-I

Prior to SQL 2005, Varchar and NVarchar holds only 8000 bytes, so those days sql programmers used TEXT datatype when their requirement ask to store more than 8000 byte of data.

The problem with this approach was that most of the string function does not work with text datatype. But after the introduction of Varchar(MAX) / NVarchar(MAX) life of programmers became easy to handle long string.

Now the question is how much Varchar(Max) datatype holds, what is the limitation for MA X? The answer is 2GB…

Oh! That nice space J

So can I declare a string variable of length 9000 byte like this Varchar(9000)?

Let’s find out:

create table #tempo

(Id int identity(1,1),

 EmpName varchar(20),

 EmpProfile varchar(9000)

)

Msg 131, Level 15, State 2, Line 5

The size (9000) given to the column 'EmpProfile' exceeds the maximum allowed for any data type (8000).

Answer we found is No; we can’t declare a column with varchar size greater than 8000. You can either declare a variable as VARCHAR(8000) or VARCHAR(MAX). But none in between.

This leads to the question:

If my requirement says to declare field with varchar(9000) size how could I do this?

How do we restrict the length of the field then?

This can be achieved by adding a CHECK constraint.

create table Students (

    student_id int,   

    notes VARCHAR(MAX))

GO

-- then alter the table to add the check constraint

-- which will restrict the the size of notes column

alter table Students

add constraint StudentNoteLength

check (DATALENGTH(notes) <= 9000)

Command(s) completed successfully.

Storage of NVARCHAR(MAX)/VARCHAR (MAX) values

The datatype Varchar(MAX), Nvarchar(MAX), Varbinary(MAX)  and XML data types are known as Large Value Types. The maximum size of a data row is 8060 bytes for storing user data. When you store a Large Value Type to a column, if the total size of the row (including your Large Value Type) value is less than 8060 bytes, the value is stored "in row". That means the value is stored in the same data row where other values of the same record are stored. If the Large Value Type is larger, it is stored "out of row" which means that the data is stored in another location and a pointer to the stored location will be added in the data row. Reading or writing values "out of the row" will need some additional processing and hence is not as fast as "in row" operations.

Coding; Joy what it gives

Post Reference: Vikram Aristocratic Elfin Share