User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

• UDF has No Access to Structural and Permanent Tables.
  • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
• UDF Accepts Lesser Numbers of Input Parameters.
  • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
• UDF Prohibit Usage of Non-Deterministic Built-in Functions
  • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
• UDF Returns Only One Result Set or Output Parameter
  • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
• UDF can not Call Stored Procedure
  • Only access to Extended Stored Procedure.
• UDF can not Execute Dynamic SQL or Temporary Tables
  • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
• UDF can not Return XML
  • FOR XML is not allowed in UDF
• UDF does not support SET options
  • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
• UDF does not Support Error Handling
  • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

convert varchar to int in sql

Few of the questions I receive very frequently. I have collect them search it & here is some important one's

How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.

try catch with RAISEERROR function

here we can learn how to use try catch block in sql server

as a programmer i never use try catch block in any of my query or store procedure,

but recently i got stuck in one store procedure, My SP not executed & i didnt get exact error message also,

after searching i got solution that using try catch block with RAISEERROR finction i can catch the exact error, so using this i got exact error, & i solved that within a second

so it is a good practice to use try catch in sql.

RAISEERROR function gives us exact error definition.

Example 1 : Simple TRY…CATCH without RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
SELECT 'Divide by zero error encountered.' ErrorMessage
END CATCH;
GO

ResultSet:
ErrorMessage
———————————
Divide by zero error encountered.

Example 2 : Simple TRY…CATCH with RAISEERROR function
BEGIN TRY
DECLARE @MyInt INT;
-- Following statement will create Devide by Zero Error
SET @MyInt = 1/0;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
SELECT @ErrorMessage = ERROR_MESSAGE();
RAISERROR (@ErrorMessage, 16, 1);
END CATCH;
GO

ResultSet:
Msg 50000, Level 16, State 1, Line 9
Divide by zero error encountered.

datetime conversion Solution

Hello Friends ,

recently me & my friend vinay had some issue in datetime datatype, in mis report we cant find records of same date ..... means we want records of todays date i.e 09/01/2010 and 09/01/2010

we was using simple query like

select * from table_name where columndate between '09/01/2010' and '09/01/2010'

using this query we unable to get data still there is data present in the table for the same date

then we do some R&D & we got the solution that

we have to convert our columndate to the same format as the input.


in our table date stored as a yyyy/MM/dd format & i am passing query in MM/dd/yyyy format
so for that
we change our query structure like this

select * from table_name where convert(varchar,columndate,101) between '09/01/2010' and '09/01/2010'

then we got exact records........ at last we got success............

here is the table where u will get all possible date format as you want

DATE FORMATS
Format # Query (current date: 12/30/2006) Sample
1 select convert(varchar, getdate(), 1) 12/30/06
2 select convert(varchar, getdate(), 2) 06.12.30
3 select convert(varchar, getdate(), 3) 30/12/06
4 select convert(varchar, getdate(), 4) 30.12.06
5 select convert(varchar, getdate(), 5) 30-12-06
6 select convert(varchar, getdate(), 6) 30 Dec 06
7 select convert(varchar, getdate(), 7) Dec 30, 06
10 select convert(varchar, getdate(), 10) 12-30-06
11 select convert(varchar, getdate(), 11) 06/12/30
101 select convert(varchar, getdate(), 101) 12/30/2006
102 select convert(varchar, getdate(), 102) 2006.12.30
103 select convert(varchar, getdate(), 103) 30/12/2006
104 select convert(varchar, getdate(), 104) 30.12.2006
105 select convert(varchar, getdate(), 105) 30-12-2006
106 select convert(varchar, getdate(), 106) 30 Dec 2006
107 select convert(varchar, getdate(), 107) Dec 30, 2006
110 select convert(varchar, getdate(), 110) 12-30-2006
111 select convert(varchar, getdate(), 111) 2006/12/30

TIME FORMATS
8 or 108 select convert(varchar, getdate(), 8) 00:38:54
9 or 109 select convert(varchar, getdate(), 9) Dec 30 2006 12:38:54:840AM
14 or 114 select convert(varchar, getdate(), 14) 00:38:54:840

Back up all database in single query

Hi guys, for programmer , taking backup using GUI is robust work, suppose you are working on more than 3 database then taking backup of 3 databases using GUI is time wasting job,

here is the solution you can take backup of all databases in one shot try this

you can set path as you wish

ex: @path = 'D:\dbBackup\' you have to create folder before executing query



DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\dbBackup\' -- your folder name instead of dbbackup

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

--

Generate Random Number in Sql

Here You can generate Random number using lower number and upper number


DECLARE @Random INT;
DECLARE @Upper INT;
DECLARE @Lower INT

---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 999 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

Enable , Disable Sa Login

In SQL SERVER 2005, all the login (including ‘sa’ ) can be enabled and disabled using ALTER LOGIN command.

To disable ‘sa’ login:
ALTER LOGIN sa DISABLE
GO

To enable ‘sa’ login:
ALTER LOGIN sa ENABLE
GO

Also for additional security (prevent educated guess from hackers) the name of the ‘sa’ account can be changed.
ALTER LOGIN [sa] WITH NAME = [AdminUser]
GO

Sql Logical Query processing phases

Hi all,

Here i am starting SQL Tour for us where you and me both will learn all required fundamental regarding SQL
so come with me for this beautiful tour

I am starting this tour with not typical t-sql or sql basics
i want to share with you how sql works ,how your query execute logically

So Lets start with Logical query processing phase

lets take a example

(8) select (9) distinct (11) ( top_specification .. select_list)
(1) from (left_table)
(3) (join_type) join (right_table)
(2) on (join_condition)
(4) where (where_condition)
(5) group by (group_by_list)
(6) with {cube | rollup}
(7) having (having_condition)
(10) order by(order_by_list)

don't be panic , i will explain you all this. you must be thinking why i put numbers?

First aspect of SQL that is different from other programming language is the order in which code is processed , In most programming languages code is processed in order in which it is written.

So lets start with How Sql process particular query

Each step generates a virtual table that is used as a input to the following step. this virtual tables
are not seen to caller, only table generated by the final step returned to the caller

in above example code is executed like this

1 . From : if join is present there then (cross join) join is performed between the two tables in the from clause and as a result virtual table VT1 is generated ..
ex: (from mstcustomer MC join mstdepartment MD)

2. On : the on filter is applied to VT1. only rows from which the is true are inserted to VT2 (virtual table 2)

3.Outer(join) : if an outer join is specified rows from the preserved table or tables for which match was not found are added to the rows from VT2 as outer rows, generating VT3

4.Where: where filter is apllied to VT3 . only rows from which(where_condition) is true are inserted to VT4

5.Group By : the rows from VT4 are arranged in a group based on the column list specified in group by clause . VT5 is generated

6.Cube | Roll up :supergroups are added to the rows from VT5, generating VT6

7.Having :having clause is applied to VT6 . only groups for which the is true is inserted to VT7

8.Select : the select list is processed , generating VT8

9.Distinct: duplicate rows are removed from VT8. VT9 is generated.

10.order by : the rows from VT9 are sorted according to the column list specified in the order by clause. A cursor is generated VT10

11.TOP: the specified numbers or percentage of rows is selected from the beginning of VC10. table VT11 is generated & return to caller ( that is your output)