Calling web service from sql server using sql clr

Problem : In my previous project I was been asked call web service from sql server stored procedure. So I get it done using SQL CLR. So using CLR we can run managed code inside sql server. Code that runs within the CLR is referred to as managed code. We can create stored procedures, triggers, user defined types, and user-defined aggregates in managed code. We can achieve significant performance increases because managed code compiles to native code prior to execution. We can use SQL CLR in in SQL Server 2005 and later. Why Sql CLR in sql server: In some cases some tasks are not possible by T-SQL as my requirement. So in that point we can go with SQL CLR. What tools I have used in this post Visual Studio 2015 Sql Server 2014 In Action: Create SQL Server Database project in VS 2015 Add SQL CLR C# Stored Procedure Name stored procedure As CallWebService 3. Add C# codes

read more Calling web service from sql server using sql clr

MERGE in Sql Server

From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done. Syntax Create two tables and Insert some records Then do Merge Now view TargetTable Data  

Sub Query in Sql

What is subquery? Subquery is  just a query inside of a query. Why Subquery? Break down complex logic Simplify reading Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery . Where to Use? We can put sub queries in  three main places in query. SELECT FROM WHERE Using in SELECT Access informations from tables  withing select query Query must return a single(scalar) value Creates a dynamic table. Useful for  breaking down queries. Query must be aliased. Note : I have used Adventureworks 2012 database for following queries  Using with FROM With WHERE Useful for comparing values from other tables Predicates used with subqueries IN Confirm column value exist in subquery Similar to inner join EXISTS Returns true if subquery returns value Used with correlated queries Correlated subquery Pass outer query column into subquery ALL Compares column

read more Sub Query in Sql

reading xml in sql server

So some times we want to read xml file in sql server. I am passing list of details to sql server using xml file format. It reduce several db calls when we passing list of data. Suppose we have List of Students. In List of Student we have to loop it and fire db calls to save data. To overcome this I create xml of List of Student and Pass it to sql server with one db call and then in my stored procedure I will handle those data as follow.


One of the more complex statements that we can execute inside sql server is a PIVOT statement. What it does?  PIVOT statement allows us to convert what would normally be row data into column data. Sounds Crazy… In simply let’s imagine we have a table as follow. In above table years are in rows. Let’s suppose we want to get data by years. Now it’s time to use PIVOT. Using PIVOT we can convert years into columns. PIVOT syntax SELECT <NonPivot> , <FirstPivotedColumn> , … FROM <Table containing data> PIVOT (FUNCTION(<data column>) FOR <List of pivoted columns>) AS <alias> Let’s do SELECT * FROM @Table PIVOT (SUM(Sales) FOR [Year] IN([2010],[2012]) )AS pvt The we get result