Utility generating DML statement taking just table name; smarter way of doing your work

DML statements are those which you need to start any kind of manipulation of your data; these are the statements which you use very often employ in your stored procedure to build up any Business Logic.

Just think about any table which has say 50 odd column which is very common, now writing any kind of DML statement say SELECT statement, how long it takes, probably 1 or 2 or say for if you r not taking help of sys.columns 3-4 minutes.

Now think about how many DML statements you cover in any business logic SP, yup a lot!!! Now imagine about an utility which in a second produce your required DML statement.

Fascinated about the idea, let start writing a routine which will make our daily work ease

IF OBJECT_ID('dbo.usp_getDMLText') IS NOT NULL

BEGIN 

       DROP PROCEDURE dbo.usp_getDMLText

END

GO

CREATE PROCEDURE dbo.usp_getDMLText (@TblName VARCHAR(50))

AS

DECLARE @designSelect VARCHAR(max)

DECLARE @designUpdate VARCHAR(max)

DECLARE @designDelete VARCHAR(max)

DECLARE @designDrop VARCHAR(max)

DECLARE @designTruncate VARCHAR(max)

DECLARE @schemaName VARCHAR(255)

BEGIN

       IF OBJECT_ID('tempdb.#TAB_DMLStatement') IS NOT NULL

              DROP TABLE #TAB_DMLStatement

       CREATE TABLE #TAB_DMLStatement (

              Id INT identity(1, 1)

              ,Operation VARCHAR(10)

              ,[Text] VARCHAR(max)

              )

       SELECT TOP 1 @schemaName = s.NAME

       FROM sys.tables t

       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

       WHERE t.NAME LIKE 'Address'

       SET @schemaName = @schemaName + '.'

       SELECT @designSelect = substring((

                           SELECT (', ' + Column_Name + CHAR(10))

                           FROM INFORMATION_SCHEMA.COLUMNS

                           WHERE Table_Name = @TblName

                           FOR XML PATH('')

                           ), 3, 1000)

       SET @designSelect = 'SELECT ' + @designSelect + ' FROM ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'SELECT'

              ,@designSelect

              )

       SELECT @designUpdate = substring((

                           SELECT (', ' + Column_Name + '= @p' + Column_Name + CHAR(10))

                           FROM INFORMATION_SCHEMA.COLUMNS

                           WHERE Table_Name = 'Address'

                           FOR XML PATH('')

                           ), 3, 1000)

       SET @designDelete = @designUpdate

       SET @designUpdate = 'UPDATE ' + @schemaName + @TblName + CHAR(10) + 'SET ' + @designUpdate + CHAR(10) + 'WHERE ' + REPLACE(@designUpdate, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'UPDATE'

              ,@designUpdate

              )

       SET @designDelete = 'DELETE ' + @TblName + CHAR(10) + 'WHERE ' + REPLACE(@designDelete, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'DELETE'

              ,@designDelete

              )

       SET @designDrop = 'DROP TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'DROP'

              ,@designDrop

              )

       SET @designTruncate = 'TRUNCATE TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'TRUNCATE'

              ,@designTruncate

              )

       SELECT Id

              ,Operation

              ,TEXT

       FROM #TAB_DMLStatement

       DROP TABLE #TAB_DMLStatement

END

Now you have here a routine which will generate DML statement for you taking just table name.

Lets configure it to shortcut key

For this Go to your SSMS

TOOL à OPTION à Environment à Keyboard àQuery Shortut à in Ctrl + F1 text box write your routine name i.e usp_getDMLText

Now our shortcut is set.

Now just take any table name select it and press Ctrl + F1

Eg Address

That’s all J so simple, Guys if you have any other idea, do coin up, will try to implement :)

I am happy even if I don’t get food for my daily meal, till you are with me; my SQL Server  :)

 

Post Reference: Vikram Aristocratic Elfin Share

New way of implementing Pagination in SQL Server 2012

Pagination is common requirement for any application, where we pull data in chunks depending upon the page number and size of page. Here in this article we will see how we can achieve pagination through Server 2012, now it is very easy to use and implement paging in SQL Server 2012. We only need to know these keywords (OFFSET, FETCH NEXT) with Order By Clause and we this we can start our pagination request.

Lets start our demo, here we are declaring a table Employee with three fields.

create table Employees (

empid int NOT NULL,

empname varchar(25) NOT NULL,

salary decimal(15,2)

)

Command(s) completed successfully.

Lets insert few data in the table.

declare @i int = 0

while @i < 10000

begin

insert into Employees

select round(rand() * 10000,0),'abc' + cast(round(rand()*10,0) as varchar), rand()*1000

set @i = @i + 1

end

Now since our table is ready, its time to enter implement pagination,

declare @offset int = 0

declare @next int = 5

select * from Employees  order by empid asc

OFFSET @offset ROWS

FETCH NEXT @next ROWS ONLY

empid       empname                   salary

----------- ------------------------- ---------

1           abc9                      364.48

2           abc8                      14.74

3           abc10                     618.69

4           abc10                     852.50

5           abc6                      788.89

OFFSET: The query excludes the number of records we mentioned in OFFSET. It says how many records we need to exclude

FETCH NEXT: It says how many records you want to fetch from the query.

In our example we use OFFSET as 0, that means we are not interest in skipping any row and FETCH NEXT is 5 means first five records, we are interest in.

That’s all one need to do to implement Pagination in SQL Server 2012.

SQL Server with a Tea glass makes a perfect combination to deal with darkness :)

Post Reference: Vikram Aristocratic Elfin Share