SQL Function return SQL TABLE

using table values parameter we can achieve it

Function :

CREATE FUNCTION FunctionName
(
@id INT
)
returns table as
return
(
Select * from TableName WHERE id=@id
)

How to call:

select * from FunctionName(1)

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Sanitize database inputs

A lesson to all developers 

Sanitize database inputs

 


If u had any trouble just ask, Happy to help u :)
 Stay Tune... Have a nice day... 'N happy Coding :)

Passing store procedure result to another store procedure as parameter

Passing store procedure result to another store procedure as parameter

-- First Stored ProcedureCREATE PROCEDURE SquareSP@MyFirstParam INT
AS
DECLARE @MyFirstParamSquare INT
SELECT @MyFirstParamSquare = @MyFirstParam*@MyFirstParam-- Additional CodeRETURN (@MyFirstParamSquare)GO

get current date in storeprocedure

cast(getdate() as date)
simple and sexy isit?

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Better way for multiple optional search in sql Store Procedure

Better way for multiple optional search in sql Store Procedure

i assigned multiple optional search to my junior resources a few weeks ago recently i gone through the code standards I'm bit surprised every one is different :) . they gone through row-filtering,Dynamic sql,where case in sql,Table search,IF IS NULL in sql ...

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

Get Store Procedure,User Table,System Table,Primary Key,Scalar Function Create date and modification date in SQL SERVER

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Loop in sql with break or Exit Statement

 Loop in sql with break or Exit Statement

 Loop in sql

Loop with break or Exit Statement

LEN and DATALENGTH in SQL Server

Difference between LEN and DATALENGTH in SQL Server

Len will trim the end of the string and count the length where as  DATALENGTH just count the lenght,its doesn't trim :)

Below sample query and example

select records from particular week days Or datepart in sql

select records from particular week days Or datepart in sql

SELECT AgencyId,CreatedOn FROM Agencies -- List All Records

SELECT AgencyId,CreatedOn FROM Agencies WHERE (datepart (dw, CreatedOn)) IN (7,1) -- List records Only on saturday and sunday

SELECT AgencyId,CreatedOn FROM Agencies WHERE (datepart (dw, CreatedOn)) NOT IN (5,2) -- List all Records EXCEPT Thuesday and Monday
 

If u had any trouble just ask, Happy to help u :) Stay Tune... Have a nice day... 'N happy Coding :)

QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF

When create or alter SQL object like Stored Procedure, User Defined Function in Query Analyzer, it is created with following SQL commands prefixed and suffixed. What are these – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF?

ANSI NULL ON/OFF:
This option specifies the setting for ANSI NULL comparisons. When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.

QUOTED IDENTIFIER ON/OFF:
This options specifies the setting for usage of double quotation. When this is on, double quotation mark is used as part of the SQL Server identifier (object name). This can be useful in situations in which identifiers are also SQL Server reserved words.

If u had any trouble just ask, Happy to help u :) Stay Tune... Have a nice day... 'N happy Coding :)

Remove Duplicate record from Table

Hi *.*,

create table #tbdemo (

id int identity(1,1),

StrName Nvarchar(50),

StrCity Nvarchar(50)

)

insert into #tbdemo values ('Arun','Kollam')

insert into #tbdemo values('Arun','Kollam')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Prajeesh','Kochi')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Sibi','Patnapuram')

insert into #tbdemo values('Dasapan','Kozikodi')

insert into #tbdemo values('Dasapan','Kozikodi')

select * from #tbdemo

select MIN(id) as ID from #tbdemo

GROUP BY StrName,StrCity

DELETE FROM #tbdemo

WHERE id NOT IN(select MIN(id) as ID from #tbdemo GROUP BY StrName,StrCity)

If u had any trouble just ask, Happy to help u :)
Stay Tune...
Have a nice day... 'N happy Coding :)

Assign Value to a variable on Store Procedure

Hi *.*,
Assign Value to a variable on Store Procedure. First declare a variable with prefix '@'.Then you can use both 
SET and SELECT.

ERROR: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Hi *.*,

Yet another common error while handling with datetime in sql.Worst part of this error is on code side and sql site we had given datetime as datatype.Still its their !!!!

Difference between GETDATE() and GETUTCDATE()

Hi *.*,
Playing with date-time is one of the biggest headache to me. :( . i faced a problem recently with that. Server is located at one place and client at another place  so what happened is the date doesn't change for 4.15 hrs!!!!.
Later v solved by adding GMT along with that.
Here i'm sharing the SQL script helped to get out of that.

Retrieve custom message if column is null in query

Hi *.*,

 Retrieve custom message if column value is null using query. Oracle, Mysql MSSQL all are providing different keyword for this purpose

NVL() function For Oracle

This function is used to replace NULL value with another value.

Table Sales_Data

store_name	Sales
Store A	300
Store B	NULL
Store C	150

• SELECT  NVL(Sales,’No data Avilable’) FROM Sales_Data
• SELECT SUM(NVL(Sales,100)) FROM Sales_Data;

2nd query will returns 550. This is because NULL has been replaced by 100 via the ISNULL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550. 

 ISNULL() function for SQL Server

is used to replace NULL value with another value.

SELECT ISNULL(Sales,’No data avilable’)) FROM Sales_Data;

 IFNULL Function for MySQL

This function takes two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value.

SELECT IFNULL(Sales,’No data avilable’) FROM Sales_Data;

Have a nice day... 'N happy Coding :)