Sql Server Interview Question:Procedure

What is stored PROCEDURE?
Creates a stored procedure, which is a saved collection of Transact-SQL statements that can take and return user-supplied parameters.

Procedures can be created for permanent use or for temporary use within a session (local temporary procedure) or for temporary use within all sessions (global temporary procedure).

Local or global temporary procedures can be created by preceding the procedure_name with a single number sign (#procedure_name) for local temporary procedures and a double number sign (##procedure_name) for global temporary procedures. The complete name, including # or ##, cannot exceed 128 characters.
Stored procedures can also be created to run automatically when Microsoft® SQL Server™ starts.

What is group procedure?
;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.

For example, the procedures used with an application called orders may be named orderproc;1, orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the entire group. If the name contains delimited identifiers, the number should not be included as part of the identifier; use the appropriate delimiter around procedure_name only.

What is parameter procedure?
@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 executed (unless a default for the parameter is defined).

How many parameter a stored procedure can have?
A stored procedure can have a maximum of 2,100 parameters.

What are the datatype you can use for stored procedure parameter?
data_type
Is the parameter data type. All data types, including text, ntext and image, can be used as a parameter for a stored procedure.

Which procedure you can use cursor datatype?
However, the cursor data type can be used only on OUTPUT parameters. When you specify a data type of cursor, the VARYING and OUTPUT keywords must also be specified

What is the maximum number of output parameters a procedure can have?
There is no limit on the maximum number of output parameters that can be of cursor data type.

What is VARYING in procedure?
Specifies the result set supported as an output parameter (constructed dynamically by the stored procedure and whose contents can vary). Applies only to cursor parameters.

Can we have default parameter procedure?
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

What does the OUTPUT keyword in procedure?
Indicates that the parameter is a return parameter. The value of this option can be returned to EXEC[UTE]. Use OUTPUT parameters to return information to the calling procedure. Text, ntext, and image parameters can be used as OUTPUT parameters. An output parameter using the OUTPUT keyword can be a cursor placeholder.

What is RECOMPILE and ENCRYPTION in stored procedure?
RECOMPILE indicates that SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Use the RECOMPILE option when using atypical or temporary values without overriding the execution plan cached in memory.

ENCRYPTION indicates that SQL Server encrypts the syscomments table entry containing the text of the CREATE PROCEDURE statement. Using ENCRYPTION prevents the procedure from being published as part of SQL Server replication

What is the maximum size of the stored procedure?
The maximum size of a stored procedure is 128 MB.

How do you get Information About Stored Procedures?
To display the text used to create the procedure, execute sp_helptext in the database in which the procedure exists with the procedure name as the parameter.

Can we get the text of encrypted procedure?
Stored procedures created with the ENCRYPTION option cannot be viewed with sp_helptext.

How do you know what are the objects referenced by procedure?
For a report on the objects referenced by a procedure, use sp_depends.

Where are the procedure name and its text stored in sql server?
When a CREATE PROCEDURE statement is executed successfully, the procedure name is stored in the sysobjects system table and the text of the CREATE PROCEDURE statement is stored in syscomments. When executed for the first time, the procedure is compiled to determine an optimal access plan to retrieve the data.

Rules for Cursor Output Parameters:
The following rules pertain to cursor output parameters when the procedure is executed:

For a forward-only cursor, the rows returned in the cursor's result set are only those rows at and beyond the position of the cursor at the conclusion of the stored procedure executed, for example:

A nonscrollable cursor is opened in a procedure on a result set named RS of 100 rows.

The procedure fetches the first 5 rows of result set RS.

The procedure returns to its caller.

The result set RS returned to the caller consists of rows from 6 through 100 of RS, and the cursor in the caller is positioned before the first row of RS.

For a forward-only cursor, if the cursor is positioned before the first row upon completion of the stored procedure, the entire result set is returned to the calling batch, stored procedure, or trigger. When returned, the cursor position is set before the first row.

For a forward-only cursor, if the cursor is positioned beyond the end of the last row upon completion of the stored procedure, an empty result set is returned to the calling batch, stored procedure, or trigger.

Note An empty result set is not the same as a null value.

For a scrollable cursor, all the rows in the result set are returned to the calling batch, stored procedure, or trigger at the conclusion of the execution of the stored procedure. When returned, the cursor position is left at the position of the last fetch executed in the procedure.

For any type of cursor, if the cursor is closed, then a null value is passed back to the calling batch, stored procedure, or trigger. This will also be the case if a cursor is assigned to a parameter, but that cursor is never opened.

Note The closed state matters only at return time. For example, it is valid to close a cursor part way through the procedure, to open it again later in the procedure, and return that cursor's result set to the calling batch, stored procedure, or trigger.

Notes about procedure:
A user-defined stored procedure can be created only in the current database (except for temporary procedures, which are always created in tempdb).

The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.

Parameters are nullable by default. If a NULL parameter value is passed and that parameter is used in a CREATE or ALTER TABLE statement in which the column referenced does not allow NULLs, SQL Server generates an error.

To prevent passing a NULL parameter value to a column that does not allow NULLs, add programming logic to the procedure or use a default value (with the DEFAULT keyword of CREATE or ALTER TABLE) for the column.

It is recommended that you explicitly specify NULL or NOT NULL for each column in any CREATE TABLE or ALTER TABLE statement in a stored procedure, such as when creating a temporary table.

The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way SQL Server assigns the NULL or NOT NULL attributes to columns if not specified in a CREATE TABLE or ALTER TABLE statement.

If a connection executes a stored procedure with different settings for these options than the connection that created the procedure, the columns of the table created for the second connection can have different nullability and exhibit different behaviors.

If NULL or NOT NULL is explicitly stated for each column, the temporary tables are created with the same nullability for all connections that execute the stored procedure.
SQL Server saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a stored procedure is created or altered. These original settings are used when the stored procedure is executed.

Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored during stored procedure execution. SET QUOTED_IDENTIFIER and SET ANSI_NULLS statements that occur within the stored procedure do not affect the functionality of the stored procedure.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a stored procedure is created or altered.

If the logic of the stored procedure is dependent on a particular setting, include a SET statement at the start of the procedure to ensure the proper setting.

When a SET statement is executed from a stored procedure, the setting remains in effect only until the stored procedure completes.
The setting is then restored to the value it had when the stored procedure was called. This allows individual clients to set the options wanted without affecting the logic of the stored procedure.

How do you ALTER PROCEDURE
Alters a previously created procedure, created by executing the CREATE PROCEDURE statement, without changing permissions and without affecting any dependent stored procedures or triggers

What are System Stored Procedures
Many administrative and informational activities in Microsoft® SQL Server™ can be performed through system stored procedures. The system stored procedures are grouped into these categories.

Category Description
Active Directory Procedures Used to register instances of SQL Server and SQL Server databases in Microsoft Windows® 2000 Active Directory™.

Catalog Procedures Implements ODBC data dictionary functions and isolates ODBC applications from changes to underlying system tables.
Cursor Procedures Implements cursor variable functionality.
Database Maintenance Plan Procedures Used to set up core maintenance tasks necessary to ensure database performance.

Distributed Queries Procedures Used to implement and manage Distributed Queries.
Full-Text Search Procedures Used to implement and query full-text indexes.
Log Shipping Procedures Used to configure and manage log shipping.

OLE Automation Procedures Allows standard OLE automation objects to be used within a standard Transact-SQL batch.

Replication Procedures Used to manage replication.
Security Procedures Used to manage security.

SQL Mail Procedures Used to perform e-mail operations from within SQL Server.
SQL Profiler Procedures Used by SQL Profiler to monitor performance and activity.
SQL Server Agent Procedures Used by SQL Server Agent to manage scheduled and event-driven activities.

System Procedures Used for general maintenance of SQL Server.
Web Assistant Procedures Used by the Web Assistant.
XML Procedures Used for Extensible Markup Language (XML) text management.
General Extended Procedures Provides an interface from SQL Server to external programs for various maintenance activities.

What are catalog procedures?


Catalog Procedures
sp_column_privileges
sp_special_columns

sp_columns
sp_sproc_columns

sp_databases
sp_statistics

sp_fkeys
sp_stored_procedures

sp_pkeys
sp_table_privileges

sp_server_info
sp_tables

What are the cursor procedure?
Cursor Procedures
sp_cursor_list
sp_describe_cursor_columns

sp_describe_cursor
sp_describe_cursor_tables



What are the database maintenance procedure?
Database Maintenance Plan Procedures
sp_add_maintenance_plan
sp_delete_maintenance_plan_db

sp_add_maintenance_plan_db
sp_delete_maintenance_plan_job

sp_add_maintenance_plan_job
sp_help_maintenance_plan

sp_delete_maintenance_plan

What are the distributed queries procedure?

Distributed Queries Procedures
sp_addlinkedserver
sp_indexes

sp_addlinkedsrvlogin
sp_linkedservers

sp_catalogs
sp_primarykeys

sp_column_privileges_ex
sp_serveroption

sp_columns_ex
sp_table_privileges_ex

sp_droplinkedsrvlogin
sp_tables_ex

sp_foreignkeys

What are the full-text search procedure

Full-Text Search Procedures
sp_fulltext_catalog
sp_help_fulltext_catalogs_cursor

sp_fulltext_column
sp_help_fulltext_columns

sp_fulltext_database
sp_help_fulltext_columns_cursor

sp_fulltext_service
sp_help_fulltext_tables

sp_fulltext_table
sp_help_fulltext_tables_cursor

sp_help_fulltext_catalogs


What are the log-shipping procedures?

Log Shipping Procedures
sp_add_log_shipping_database
sp_delete_log_shipping_database

sp_add_log_shipping_plan
sp_delete_log_shipping_plan

sp_add_log_shipping_plan_database
sp_delete_log_shipping_plan_database

sp_add_log_shipping_primary
sp_delete_log_shipping_primary

sp_add_log_shipping_secondary
sp_delete_log_shipping_secondary

sp_can_tlog_be_applied
sp_get_log_shipping_monitor_info

sp_change_monitor_role
sp_remove_log_shipping_monitor

sp_change_primary_role
sp_resolve_logins

sp_change_secondary_role
sp_update_log_shipping_monitor_info

sp_create_log_shipping_monitor_account
sp_update_log_shipping_plan

sp_define_log_shipping_monitor
sp_update_log_shipping_plan_database


OLE Automation Extended Stored Procedures
sp_OACreate
sp_OAMethod

sp_OADestroy
sp_OASetProperty

sp_OAGetErrorInfo
sp_OAStop

sp_OAGetProperty
Object Hierarchy Syntax


Replication Procedures
sp_add_agent_parameter
sp_enableagentoffload

sp_add_agent_profile
sp_enumcustomresolvers

sp_addarticle
sp_enumdsn

sp_adddistpublisher
sp_enumfullsubscribers

sp_adddistributiondb
sp_expired_subscription_cleanup

sp_adddistributor
sp_generatefilters

sp_addmergealternatepublisher
sp_getagentoffloadinfo

sp_addmergearticle
sp_getmergedeletetype

sp_addmergefilter
sp_get_distributor

sp_addmergepublication
sp_getqueuedrows

sp_addmergepullsubscription
sp_getsubscriptiondtspackagename

sp_addmergepullsubscription_agent
sp_grant_publication_access

sp_addmergesubscription
sp_help_agent_default

sp_addpublication
sp_help_agent_parameter

sp_addpublication_snapshot
sp_help_agent_profile

sp_addpublisher70
sp_helparticle

sp_addpullsubscription
sp_helparticlecolumns

sp_addpullsubscription_agent
sp_helparticledts

sp_addscriptexec
sp_helpdistpublisher

sp_addsubscriber
sp_helpdistributiondb

sp_addsubscriber_schedule
sp_helpdistributor

sp_addsubscription
sp_helpmergealternatepublisher

sp_addsynctriggers
sp_helpmergearticle

sp_addtabletocontents
sp_helpmergearticlecolumn

sp_adjustpublisheridentityrange
sp_helpmergearticleconflicts

sp_article_validation
sp_helpmergeconflictrows

sp_articlecolumn
sp_helpmergedeleteconflictrows

sp_articlefilter
sp_helpmergefilter

sp_articlesynctranprocs
sp_helpmergepublication

sp_articleview
sp_helpmergepullsubscription

sp_attachsubscription
sp_helpmergesubscription

sp_browsesnapshotfolder
sp_helppublication

sp_browsemergesnapshotfolder
sp_help_publication_access

sp_browsereplcmds
sp_helppullsubscription

sp_change_agent_parameter
sp_helpreplfailovermode

sp_change_agent_profile
sp_helpreplicationdboption

sp_changearticle
sp_helpreplicationoption

sp_changedistpublisher
sp_helpsubscriberinfo

sp_changedistributiondb
sp_helpsubscription

sp_changedistributor_password
sp_ivindexhasnullcols

sp_changedistributor_property
sp_helpsubscription_properties

sp_changemergearticle
sp_link_publication

sp_changemergefilter
sp_marksubscriptionvalidation

sp_changemergepublication
sp_mergearticlecolumn

sp_changemergepullsubscription
sp_mergecleanupmetadata

sp_changemergesubscription
sp_mergedummyupdate

sp_changepublication
sp_mergesubscription_cleanup

sp_changesubscriber
sp_publication_validation

sp_changesubscriber_schedule
sp_refreshsubscriptions

sp_changesubscriptiondtsinfo
sp_reinitmergepullsubscription

sp_changesubstatus
sp_reinitmergesubscription

sp_change_subscription_properties
sp_reinitpullsubscription

sp_check_for_sync_trigger
sp_reinitsubscription

sp_copymergesnapshot
sp_removedbreplication

sp_copysnapshot
sp_repladdcolumn

sp_copysubscription
sp_replcmds

sp_deletemergeconflictrow
sp_replcounters

sp_disableagentoffload
sp_repldone

sp_drop_agent_parameter
sp_repldropcolumn

sp_drop_agent_profile
sp_replflush

sp_droparticle
sp_replicationdboption

sp_dropanonymouseagent
sp_replication_agent_checkup

sp_dropdistpublisher
sp_replqueuemonitor

sp_dropdistributiondb
sp_replsetoriginator

sp_dropmergealternatepublisher
sp_replshowcmds

sp_dropdistributor
sp_repltrans

sp_dropmergearticle
sp_restoredbreplication

sp_dropmergefilter
sp_revoke_publication_access

sp_scriptsubconflicttable

sp_dropmergepublication
sp_script_synctran_commands

sp_dropmergepullsubscription
sp_setreplfailovermode

sp_showrowreplicainfo

sp_dropmergesubscription
sp_subscription_cleanup

sp_droppublication
sp_table_validation

sp_droppullsubscription
sp_update_agent_profile

sp_dropsubscriber
sp_validatemergepublication

sp_dropsubscription
sp_validatemergesubscription

sp_dsninfo
sp_vupgrade_replication

sp_dumpparamcmd


Security Procedures
sp_addalias
sp_droprolemember

sp_addapprole
sp_dropserver

sp_addgroup
sp_dropsrvrolemember

sp_addlinkedsrvlogin
sp_dropuser

sp_addlogin
sp_grantdbaccess

sp_addremotelogin
sp_grantlogin

sp_addrole
sp_helpdbfixedrole

sp_addrolemember
sp_helpgroup

sp_addserver
sp_helplinkedsrvlogin

sp_addsrvrolemember
sp_helplogins

sp_adduser
sp_helpntgroup

sp_approlepassword
sp_helpremotelogin

sp_changedbowner
sp_helprole

sp_changegroup
sp_helprolemember

sp_changeobjectowner
sp_helprotect

sp_change_users_login
sp_helpsrvrole

sp_dbfixedrolepermission
sp_helpsrvrolemember

sp_defaultdb
sp_helpuser

sp_defaultlanguage
sp_MShasdbaccess

sp_denylogin
sp_password

sp_dropalias
sp_remoteoption

sp_dropapprole
sp_revokedbaccess

sp_dropgroup
sp_revokelogin

sp_droplinkedsrvlogin
sp_setapprole

sp_droplogin
sp_srvrolepermission

sp_dropremotelogin
sp_validatelogins

sp_droprole


SQL Mail Procedures
sp_processmail
xp_sendmail

xp_deletemail
xp_startmail

xp_findnextmsg
xp_stopmail

xp_readmail


SQL Profiler Procedures
sp_trace_create
sp_trace_setfilter

sp_trace_generateevent
sp_trace_setstatus

sp_trace_setevent


SQL Server Agent Procedures
sp_add_alert
sp_help_jobhistory

sp_add_category
sp_help_jobschedule

sp_add_job
sp_help_jobserver

sp_add_jobschedule
sp_help_jobstep

sp_add_jobserver
sp_help_notification

sp_add_jobstep
sp_help_operator

sp_add_notification
sp_help_targetserver

sp_add_operator
sp_help_targetservergroup

sp_add_targetservergroup
sp_helptask

sp_add_targetsvrgrp_member
sp_manage_jobs_by_login

sp_addtask
sp_msx_defect

sp_apply_job_to_targets
sp_msx_enlist

sp_delete_alert
sp_post_msx_operation

sp_delete_category
sp_purgehistory

sp_delete_job
sp_purge_jobhistory

sp_delete_jobschedule
sp_reassigntask

sp_delete_jobserver
sp_remove_job_from_targets

sp_delete_jobstep
sp_resync_targetserver

sp_delete_notification
sp_start_job

sp_delete_operator
sp_stop_job

sp_delete_targetserver
sp_update_alert

sp_delete_targetservergroup
sp_update_category

sp_delete_targetsvrgrp_member
sp_update_job

sp_droptask
sp_update_jobschedule

sp_help_alert
sp_update_jobstep

sp_help_category
sp_update_notification

sp_help_downloadlist
sp_update_operator

sp_helphistory
sp_update_targetservergroup

sp_help_job
sp_updatetask

xp_sqlagent_proxy_account


System Procedures
sp_add_data_file_recover_suspect_db
sp_helpconstraint

sp_addextendedproc
sp_helpdb

sp_addextendedproperty
sp_helpdevice

sp_add_log_file_recover_suspect_db
sp_helpextendedproc

sp_addmessage
sp_helpfile

sp_addtype
sp_helpfilegroup

sp_addumpdevice
sp_helpindex

sp_altermessage
sp_helplanguage

sp_autostats
sp_helpserver

sp_attach_db
sp_helpsort

sp_attach_single_file_db
sp_helpstats

sp_bindefault
sp_helptext

sp_bindrule
sp_helptrigger

sp_bindsession
sp_indexoption

sp_certify_removable
sp_invalidate_textptr

sp_configure
sp_lock

sp_create_removable
sp_monitor

sp_createstats
sp_procoption

sp_cycle_errorlog
sp_recompile

sp_datatype_info
sp_refreshview

sp_dbcmptlevel
sp_releaseapplock

sp_dboption
sp_rename

sp_dbremove
sp_renamedb

sp_delete_backuphistory
sp_resetstatus

sp_depends
sp_serveroption

sp_detach_db
sp_setnetname

sp_dropdevice
sp_settriggerorder

sp_dropextendedproc
sp_spaceused

sp_dropextendedproperty
sp_tableoption

sp_dropmessage
sp_unbindefault

sp_droptype
sp_unbindrule

sp_executesql
sp_updateextendedproperty

sp_getapplock
sp_updatestats

sp_getbindtoken
sp_validname

sp_help
sp_who


Web Assistant Procedures
sp_dropwebtask
sp_makewebtask

sp_enumcodepages
sp_runwebtask


XML Procedures
sp_xml_preparedocument
sp_xml_removedocument


General Extended Procedures
xp_cmdshell
xp_logininfo

xp_enumgroups
xp_msver

xp_findnextmsg
xp_revokelogin

xp_grantlogin
xp_sprintf

xp_logevent
xp_sqlmaint

xp_loginconfig
xp_sscanf


How do you DROP a PROCEDURE?
Removes one or more stored procedures or procedure groups from the current database.
Syntax
DROP PROCEDURE { procedure } [ ,...n ]

How do you recompile a procedure?
sp_recompile
Causes stored procedures and triggers to be recompiled the next time they are run.
Syntax
sp_recompile [ @objname = ] 'object'
sp_recompile looks for an object in the current database only.
The queries used by stored procedures and triggers are optimized only when they are compiled. As indexes or other changes that affect statistics are made to the database, compiled stored procedures and triggers may lose efficiency. By recompiling stored procedures and triggers that act on a table, you can reoptimize the queries.

Note Microsoft® SQL Server™ automatically recompiles stored procedures and triggers when it is advantageous to do so.

What are the Rules for Programming Stored Procedures ?
Rules for programming stored procedures include:

The CREATE PROCEDURE definition itself can include any number and type of SQL statements except for the following CREATE statements, which cannot be used anywhere within a stored procedure:

CREATE DEFAULT CREATE TRIGGER
CREATE PROCEDURE CREATE VIEW
CREATE RULE

Other database objects can be created within a stored procedure. You can reference an object created in the same stored procedure as long as it is created before it is referenced.

You can reference temporary tables within a stored procedure.
If you create a local temporary table inside a stored procedure, the temporary table exists only for the purposes of the stored procedure; it disappears when you exit the stored procedure.

If you execute a stored procedure that calls another stored procedure, the called stored procedure can access all objects created by the first stored procedure, including temporary tables.


If you execute a remote stored procedure that makes changes on a remote instance of Microsoft® SQL Server™ 2000, those changes cannot be rolled back. Remote stored procedures do not take part in transactions.

The maximum number of parameters in a stored procedure is 2100.
The maximum number of local variables in a stored procedure is limited only by available memory.

Depending on available memory, the maximum size of a stored procedure is 128 megabytes (MB).


How do you Encrypt Procedure?
If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause.
The procedure definition is then stored in an unreadable form.
After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.

What is EXECUTE in procedure?
Executes a scalar-valued, user-defined function, a system procedure, a user-defined stored procedure, or an extended stored procedure. Also supports the execution of a character string within a Transact-SQL batch.