Showing posts with label Difference. Show all posts
Showing posts with label Difference. Show all posts

SQL Server: Difference between SQL Clause and Statement

This might be a silly discussions on my blog. But some learners like me who are not familiar with English language may get confused between the two. Thus I have posted this article.
As the name implies, SQL Statement is made up of one or more SQL Clauses. To say with an example
SELECT Items FROM inventory WHERE ItemName = 'glasscups' HAVING COUNT(*)>1
The whole content inside the above block is a SQL Statement. Where as SELECT, FROM, WHERE, HAVING are SQL Clauses.

Difference between Stored Procedures and Functions

Stored Procedure (SP) Function (User Defined Function - UDF)
SPs are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called Functions are compiled and executed every time when it is called
SP can return zero, single or multiple values Function should return atleast one value
We can use Transactions in SP Cannot use transactions in UDF
SPs can use SELECT, UPDATE, INSERT, DELETE, DROP statements Functions are computed values and only SELECT statement is valid
SPs cannot be utilised in SELECT statements Functions can be embedded in SELECT statement
SP can have input and output parameter UDFs can have only input parameters
SPs can can execute with or without parameters UDFs needs at least one input parameter. Without parameter UDFs can be created but it would be a insignificant one
We call function from SP We cannot call SP from an UDF
Exception handlers like TRY-CATCH blocks can be used in SP Cannot use Exception handlers can be used in UDF