Xpert-Zone.blogspot.com

Showing posts with label SQL SERVER 2005-2008. Show all posts

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 ....