Case Statement in SQL Stored Procedure

create procedure getdataRaj
        @en varchar(20),@s int
        as
        begin
                select dbo.Emp100.Eno,ename,sal    from Emp100
                where ename =
                            CASE ename   
                                    WHEN @en THEN ename
                            end
                            or sal=
                            CASE sal
                                    WHEN @s THEN Sal
                            end
        end
                       

select * from Emp100       
exec getdataRaj    Null,NULL
exec getdataRaj    'Jaggu',NULL
exec getdataRaj    NULL,9000
exec getdataRaj 'SAI',NULL

What are the differences between stored procedure and functions

functions are used for computations where as procedures 
can be used for performing business logic
2) functions MUST return a value, procedures need not be.
3) you can have DML(insert, update, delete) statements in a 
function. But, you cannot call such a function in a SQL 
query..eg: suppose, if u have a function that is updating a 
table.. you can't call that function in any sql query.- 
select myFunction(field) from sometable; will throw error.
4) function parameters are always IN, no OUT is possible

Procedure may return none or more values.Function must always return one value either a scalar value or a table.
Procedure have input,output parameters.Functions have only input parameters.
Stored procedures are called independently by EXEC command whereas Functions are called from within SQL statement.
Functions can be called from procedure.Procedures cannot be called from function.
Exception can be handled in Procedure by try-catch block but try-catch block cannot be used in a function.(error-handling)
Transaction management possible in procedure but not in function.




exec ufn_GetMaxValue 1,2,3,4
CREATE FUNCTION [dbo].[ufn_GetMaxValue]
( @pInt1 INT, @pInt2 INT, @pInt3 INT, @pInt4 INT )
RETURNS INT
AS
BEGIN
DECLARE @IntTable TABLE ( [IntValue] INT )
DECLARE @MaxValue INT

INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt1 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt2 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt3 )
INSERT INTO @IntTable ( [IntValue] ) VALUES ( @pInt4 )

SELECT @MaxValue = MAX( [IntValue] )
FROM @IntTable

RETURN @MaxValue
END
GO

Coding to get nth record in sql server

for 10 th record

select top 1 * from (select top 10 * from dEmployeenew order by 1 desc)ac

choose 10 with nth

How to convert from string to date / datetime? in sql server

Execute the following T-SQL scripts in Microsoft SQL Server Manangement Studio Query Editor to demonstrate T-SQL convert and cast functions in transforming string date, string time & string datetime data to datetime data type. T-SQL date / datetime functions usage examples are presented as well.

-- SQL Server string to date / datetime conversion - datetime string format sql server

-- MSSQL string to datetime conversion - convert char to date - convert varchar to date

-- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century)

SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000



-- Without century (yy) string date conversion - convert string to datetime function

SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM)

SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000



-- Convert string to datetime sql - convert string to date sql - sql dates format

-- T-SQL convert string to datetime - SQL Server convert string to date

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd

SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy

SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy

SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy

-- mon types are nondeterministic conversions, dependent on language setting

SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy

SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss

-- 1900-01-01 20:10:44.000



-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format

SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109)

-- 2016-10-23 11:02:44.013

SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy

SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd

-- YYYYMMDD ISO date format works at any language setting - international standard

SELECT convert(datetime, '20161023')

SELECT convert(datetime, '20161023', 112) -- yyyymmdd

-- 2016-10-23 00:00:00.000

SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm

-- 2016-10-23 11:02:07.577

SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h)

-- 1900-01-01 20:10:25.300

SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h)

-- 2016-10-23 20:44:11.000

SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

-- 2016-10-23 20:44:11.500


-- Style 126 is ISO 8601 format: international standard - works with any language setting

SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm)

-- 2008-10-23 18:52:47.513


-- Convert DDMMYYYY format to datetime - sql server to date / datetime

SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105)

-- 2016-01-31 00:00:00.000

-- SQL Server T-SQL string to datetime conversion without century - some exceptions
-- nondeterministic means language setting dependent such as Mar/Mär/mars/márc

SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default

SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S.

SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI

SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR

SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German

SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian

SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det.

SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det.

SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss

SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec

SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S.

SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan

SELECT convert(datetime, '161023', 12) -- yymmdd ISO

SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt

SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h)

SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can.

SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC

Preventing the Drop and Alter table in Sql Server Using DDL Trigger

In the following example, DDL trigger safety will fire whenever a DROP_TABLE or ALTER_TABLE
event occurs in the database.

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK

Difference between sp_executesql and EXEC() in Sql Server

EXEC() - whose first parameter is a parameterized SQL statement.
sp_executesql - The second parameter is a parameter-list declaration, similar to the parameter
list present in the declaration of a stored procedure.

If the malicious user enters a value as Sql Injection EXEC() execute the maliculous code
but sp_executesql It will search for the value ' or '1'='1 as product name in the database.
Thus preventing SQL Injection attacks.

How to Reset Auto Increment ID + SQL Server

USE dbname
GO
DBCC CHECKIDENT (tablename, RESEED, 0)
GO

it will reset to 0 again, but u r col is primary key not continue insert rows because in that col is not support duplicates.


u r column only auto increment then support but duplicates is there.

but u need last all rows is deleted and reset auto increment value assigned exec perfectly.