Can any body tell me what is Store procedure in SQl Server..?

More or less, its code that resides on the server and gets compiled when used.

A Stored Procedure (SP) is a chunk of code that is run by the database server, usually (if not always) triggered by a database event. SPs accept parameters and, I believe, can have local variables and can access database tables and fields. Triggers are defined to execute given SPs when given database events happen. For example, a trigger might be defined to run the SP NewUser when a record is added the the User table. The NewUser SP might make some changes to other tables based upon the record just added. I've never used SQL Server SPs but believe that what I've said is generic enough that it should apply to SQL Server also. SQL Server might have a different name for what I call a 'trigger'.

This is database scripting. Most modern DBMS's are operating environments unto themselves (similar to web servers). They handle permissions, multidimensional file structures, multiuser input, network connections, parsing, and host various utilities. As with all mature environments, a DBMS has some level of scripting. When you say stored procedure, most people assume you mean SQL statements stored by the server which users can call to be queried. These are usually not just basic SQL, but SQL with some additional scripting syntax. Examples are SQL Server's T-SQL, Oracle's PL/SQL, and IBM DB2's (and MySQL 5.0's) SQL:2003. Some DBMS's support multiple languages.