Current Headline

Listen to Your Daily Tune's

Saturday, October 02, 2010

Stored Procedure-Update

--update stored procedure

CREATE PROC Sp_updatexpertzone (@id     INT,

                                @name1  VARCHAR(20),

                                @xdate  DATETIME,

                                @salary INT)

AS

  UPDATE xpertzoneblogspot

  SET    name1 = @name1,
         xdate = @xdate,
         salary = @salary
  WHERE  id = @id
  --executing stored Procedure

EXEC Sp_updatexpertzone    2, 'update value', '2010-10-01',9000  --select tables again

  SELECT *  FROM   xpertzoneblogspot


The Guru's Guide to SQL Server Stored Procedures, XML, and HTML



Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET

Read More ....

Stored Procedure-Delete

--Delete Procedure

--as delete from table will be from a unique key and in our table it is primary key

CREATE PROC Sp_deletexpert (@id INT)

AS

  DELETE FROM xpertzoneblogspot WHERE  id = @id
 

  --executing delete procedure
 EXEC Sp_deletexpert 6

  --selecting database again
  SELECT *  FROM   xpertzoneblogspot 




Read More ....

Stored Procedure


--Stored Procedure

-- they are secured and pre-complied and are faster 

--Stored Procedure can be used for Insert,Delete and Update Statement's

-- we can use to name our stored procedure as usp_insert means user defined stored procedure or

--sp_insert  

CREATE PROC Sp_insertzpert (

-- defining parameter's

@id     INT,
@name1  VARCHAR(20),
@xdate  DATETIME,
@salary INT)
AS
  INSERT INTO xpertzoneblogspot  

    VALUES     (@id,
              @name1,
              @xdate,
             @salary)

  --Now Executing Stored Procedure

  EXEC Sp_insertzpert 6,'new value','2010-10-01',19000

  --Select the Database

  SELECT *  FROM   xpertzoneblogspot

 

Read More ....

Sunday, September 19, 2010

Check Constraint Sql Server 2005 Part-2


CREATE TABLE user1

  (

     id     INT IDENTITY(1, 1),

     name1  VARCHAR(20),

     salary INT CONSTRAINT chk_sal CHECK (salary>2000)

  --adding first check condition to filter database sal should be 2000

  )



--entering data

INSERT INTO user1

VALUES     ('Rambo',

            23000)



INSERT INTO user1

VALUES     ('John',

            8000)



SELECT *

FROM   user1



--Now what if we want to make changed into our check condition without effecting old data how it can be done

--1.Drop Existing Constraint

ALTER TABLE user1 DROP CONSTRAINT chk_sal



--2,Now add constraint again

ALTER TABLE user1 WITH NOCHECK ADD CONSTRAINT chk_sal CHECK(salary>9000)



SELECT *

FROM   user1



INSERT INTO user1

VALUES     ('Vertigo',

            10000) 




Read More ....

Check Constraint Sql Server 2005


--check constraint it set's the particular condition until 

--unless it is fulfill the record will not entered into the database

CREATE TABLE user2

  (

     id     INT IDENTITY(1, 1),

     name1  VARCHAR(20) CHECK (name1 LIKE '[%t%]'),

     --checking name enter in database Should start from T

     salary INT CHECK (salary>2000)

  --Checking No Employee should get salary less than 2000

  )



INSERT INTO user2

VALUES     ('ramabo',

            1000) -- will throw an execption as we made filter our database with check condition

INSERT INTO user2

VALUES     ('t',

            23000)



SELECT * FROM   user2 


Read More ....

Constraint's in Sql Server 2005-2008

--Constraint's in Sql Server 2005-2008

--Not Null and Default

CREATE TABLE user1

  (

     sno        INT IDENTITY(1, 1),

     id         INT NOT NULL,-- means the value cannot be null/blank or we can say mandatory feild needs to be filled

     name1      VARCHAR(20),

     orderdate  DATETIME DEFAULT Getdate(),--if entered as blank by default it will take current date 

     app_status VARCHAR(10) DEFAULT 'pending'--if status left blank by default for each id it will be pending

  )

INSERT INTO user1

VALUES     (101,

            'john',

            '2010-09-15',

            'approved')

INSERT INTO user1

            (id,

             name1,

             app_status)

VALUES     (103,

            'will',

            'approved')

INSERT INTO user1

            (id,

             name1)

VALUES     (104,

            'sam')

SELECT * FROM   user1


Read More ....

Saturday, September 11, 2010

Updating Table in Sql Server 2005-2008


--Data Manipulation Commands

--Updating Values in Table

UPDATE xpertzoneblogspot

SET    name1 = 'XpertBlog',

       xdate = '2010-09-11',

       salary = 20000

WHERE  id = 2



--- selecting our table again

SELECT * FROM   xpertzoneblogspot


Read More ....

Deleting Data from Table in Sql Server 2005-2008


--Data Manipulation Commands

--- how to select values from the table

SELECT *

FROM   xpertzoneblogspot



--Deleting  values in our table always perform in your database with one unique coloumn

DELETE FROM xpertzoneblogspot

WHERE  id = 1



--- selecting our table again

SELECT *

FROM   xpertzoneblogspot 





3M 17003-VP-3PK Command Adhesive Large Hooks Value Pack 
3M Command 17023P Large Mounting Replacement Strips

Read More ....

Inserting Data in Sql Table




--Data Manipulation Commands

--Inserting values in our table

INSERT INTO xpertzoneblogspot

VALUES     (1,

            'xpert',

            Getdate(),

            1000)



INSERT INTO xpertzoneblogspot

VALUES     (2,

            'Blog',

            Getdate(),

            2000)



INSERT INTO xpertzoneblogspot

VALUES     (3,

            'Spot',

            '2010-09-11',

            4000)



--- how to select values from the table

SELECT *

FROM   xpertzoneblogspot 



Microsoft SQL Server 2008 R2 Unleashed

Read More ....

Creating Tables in SQL SERVER 2005-2008


Learning SQL

Read More ....

Saturday, August 28, 2010

Use Database

Command

use xpertzoneblogspot


Read More ....

Creating Database in Sql Server 2005


Command

create database xpertzoneblogspot

Beginning SQL Server 2005 Programming (Programmer to Programmer)

Read More ....

SQL Server 2005

Start Journey to SQL SERVER 2005-2008

Read More ....

Sunday, April 04, 2010

Naruto Manga-489

Read More ....

Thought of The Day

Thought of The Day

Sometimes people are nothing ..you make them something..&when they become something...they feel you are nothing.

Read More ....

Tuesday, March 16, 2010

Thought of the Day

There are two kinds of failures: those who thought and never did, and those who did and never thought.

Read More ....

Saturday, March 06, 2010

Few Funny Facts

Few Funny Facts

The average person falls asleep in seven minutes.

A duck's quack doesn't echo, and no one knows why.

Starfish don't have brains.

Termites work 24 hours per day and they do not sleep.

16 percent of Americans will receive one speeding ticket this year and it will cost them on average $150 per ticket

New Jersey and Oregon are the only two states where it is illegal to pump your own gas.

We are born with 350 bones, and die with 206..

There are 2,500,000 rivets in the Eiffel Tower.

The word "fart" comes from the Old English "feortan" (meaning "to break wind").

Farts are flammable.

Although they won't admit it, women fart as much as men.

Farts are flammable.

A person produces about half a liter of farts a day.

Farts have been clocked at a speed of 10 feet per second.

The temperature of a fart at time of creation is 98.6 degrees Fahrenheit..

On the average a fart is composed of about 59% nitrogen, 21% hydrogen, 9% carbon dioxide, 7% methane, and 4% oxygen. Less than 1% is what makes them stink.

Babies cry but they don't produce tears until one to three months after birth. All major league baseball umpires must wear black underwear while on the job!

At 120 miles per hour, a Formula One car generates so much downforce that it can drive upside down on the roof of a tunnel.

At horse race tracks, the favorite wins fewer than 30% of the time!

Each year, 30,000 people are seriously injured by exercise equipment.

Left-handed people are better at sports that require good spatial judgment and fast reaction, compared to right-handed individuals.

No high jumper has ever been able to stay off the ground for more than one second

7,000 new insect species are discovered every year.

A bird 'chews' with its stomach.

A blue whale's aorta (the main blood vessel) is large enough for a human to crawl through.

A blue whale's heart is the size of a Volkswagen Beetle!

A chicken will lay bigger and stronger eggs if you change the lighting in such a way as to make them think a day is 28 hours long!

A cockroach will live nine days without it's head, before it starves to death

A cow gives nearly 200,000 glasses of milk in her lifetime

A cow produces 200 times more gas a day than a person.

A full-grown bear can run as fast as a horse.

A giraffe can clean its ears with its 21-inch tongue!

A hippo can open its mouth wide enough to fit a 4 foot tall child inside!

A kangaroo cannot jump if its tail is off the ground.

A lion's roar can be heard from more than five miles away.

A mole can dig a hole 300 feet deep in one night.

A rat can fall from a five story building without injury.

A polar bear's skin is black. Its fur is not white, but actually clear

A queen bee uses her stinger only to sting another queen bee.

A moth has no stomach.

Read More ....

Feburary 2nd in History

1653: New Amsterdam (later renamed New York) is founded
1914: Cub Scouts founded in England
1933: Three days after becoming chancellor, Adolf Hitler dissolves Parliament. G?g bans communist meetings/demonstrations in Germany
1943: Battle of Stalingrad ends with final surrender of the German army, leaving 600,000 dead

Read More ....

Thought of the Day

Love without passion is dreary; passion without love is horrific

Read More ....

Naruto Manga-485

Naruto Manga-485

Read More ....

Related Posts with Thumbnails

Xpert-Zone Blog Overview