XploreDotNet

Thursday, February 21, 2008

Create Stored Procedure - What Is Stored Procedure Part - II

create a simple table Book, it contain 6 fields(columns) i which rowid in identity column which generated the number in sequence order.
CREATE TABLE BOOK
(
iROWID INT IDENTITY(1,1) PRIMARY KEY,
vBOOKNAME VARCHAR(20),
vAUTHOR VARCHAR(30),
vPUBLICATION VARCHAR(50),
vEDITION VARCHAR(20),
vPRICE NUMERIC(5,2)
)

CreatingProcedure For Inserting Data Into The Table

CREATE PROC PROC_ADD_BOOK
(
//declaration Part
@vBookName VARCHAR(20),
@vAUTHOR VARCHAR(30),
@vPUBLICATION VARCHAR(50),
@vEDITION VARCHAR(20),
@vPRICE NUMERIC(5,2)
)AS
BEGIN
INSERT INTO BOOK(vBOOKNAME,vAUTHOR,vPUBLICATION,vEDITION,vPRICE)
VALUES (@vBookName,@vAUTHOR,@vPUBLICATION,@vEDITION,@vPRICE)
END

Executing The Procedure
EXEC PROC_ADD_BOOK 'XML & ASP.NET','KRIK ALLAN EVANS','PEARSON EDUCATION','2002','555'

Wednesday, February 20, 2008

What Is Stored Procedure Part - I

Stored procedures have a segment of code which contains declarative or procedural SQL statement. A stored procedure is resided in the catalog of the database, we can invoke (call) it from a program, stored procedure or even from a trigger.

Stored Procedure contains sql statements like insert, update and delete. In addition stored procedure supports If and while statements.

Advantages:
Once we created Stored Procedure then it can be reused over and over again by multiple applications.

Stored Procedure increase the performance of the application because once it compiled successfully then it’s stored in database catalog. When applications call them, they generally execute faster when compared with un-compiled SQL Statements which are sent from the applications.

Network Traffic between application server and database server is also signification reduced when compared with un-compiled SQL Statements which are sent from the applications.

Syntax
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]

[ WITH
{ RECOMPILE ENCRYPTION RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

owner:
Is the name of the user ID that owns the stored procedure. owner must be either the name of the current user or the name of a role that a current user is a member.

procedure_name:
Is the name of the new stored procedure, procedure name cannot exceed 128 characters.

@parameter:
Is a parameter in the procedure. One or more parameters can be declared in a CREATE PROCEDURE statement. The value of each declared parameter must be supplied by the user when the procedure is .A stored procedure can have a maximum of 2,100 parameters. Specify a parameter name using an at sign (@) as the first character.

OUTPUT:
Indicates that the parameter is a return parameter.

;number:
Is an optional integer used to group procedures of the same name so they can be dropped together with a single DROP PROCEDURE statement.

Tuesday, January 15, 2008

Try Visual Studio 2008

Microsoft Visual Studio 2008 provides an industry-leading developer experience for Windows Vista, the 2007 Microsoft Office system, and the Web.

90-Day Trial Downloads

Visual Studio 2008 Professional Edition
Visual Studio Team System 2008 Team Suite
Visual Studio Team System 2008 Team Foundation Server
Visual Studio Team System 2008 Test Load Agent

Courtesy

Friday, January 11, 2008

javascript function for invoking button click event

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>javascript function for invoking btnclick event</title>
<script language="javascript" type="text/javascript" >
function TrigButton()
{
if(window.event.keyCode == 13)
{
if(document.getElementById('txt').value.length > 0)
{
document.getElementById('bt').focus();
document.getElementById('bt').click();
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="txt" runat="server" Style="z-index: 105; left: 41px; position: absolute;
top: 141px" onkeydown="TrigButton()" ></asp:TextBox>
<asp:Button ID="bt" runat="server" Style="z-index: 107; left: 205px; position: absolute;
top: 142px" Text="Button" OnClientClick="javascript:alert('Button Triggered');" OnClick="bt_Click"/>
</div>
</form>
</body>
</html>

Tuesday, December 11, 2007

How To Find What Are All The Tables Available In Particular Schema In SQL SEREVER

Query To List All The Tables In a Particular Schema

Query

SELECT * FROM INFORMATION_SCHEMA.TABLES

Description
This Query List All The Tables In a Particular Schema(Database).

For Example
If You Are in the Pubs Schema(Database) In SQL SERVER,This Query List All The Tables available within the particular Schema including systable and user table

Query To List All The Columns Available In A Particular Schema

Query
SELECT * FROM INFORMATION_SCHEMA.COLUMNS

Description
This Query List All The Columns Available In A Particular Schema(Database)

For Example
If You Are in the Pubs Schema(Database) In SQL SERVER, This Query List All The Columns available within the particular Schema

Query To List All The Users Tables In a Particular Schema

Query
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME

Description
This Query List All The Users Tables In a Particular Schema(Database).

For Example
If You Are in the Pubs Schema(Database) In SQL SERVER, This Query List All The Users Tables available within the particular Schema

Query To List All The Columns Available In A Particular Schema

Query
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AUTHORS'

Description
This Query List All The Columns Available In A Particular Schema(Database) for the Particular Table Authors

To Find The Primary Key Of The Table.

Syntax:

SP_PKEYS YourTableName

Description
This System Procedure Returns the Primary Key Reference Of The Particular Table In That Schema(Database).

Example: SP_PKEYS AUTHORS

Note:Giving the tablename within the single quotes is not mandatory

To Find The Foreign Key Of The Table.

Syntax:

SP_FKEYS YourTableName

Description
This System Procedure Returns the Foreign Key Reference Of The Particular Table In That Schema(Database).

Example: SP_FKEYS AUTHORS
Note: Giving the tablename within the single quotes is not mandatory