CodeLogs

This is handy, bumped into this and I thought it would be nice to take note and share. What this code illustrate is that you can basically use a stored procedure's result and insert it into a table and do your own query. Now why do we want to do that? Say for example the existing query does something and is being used by different modules and touching it would require us to check/test those modules that has a dependency on the  SP. Applying the sample below allows us to just reuse the existing SP and get the resulting data and do our own filters, grouping etc.. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 DECLARE @ Table TABLE ( SPID INT , Status VARCHAR ( MAX ), LOGIN VARCHAR ( MAX ), HostName VARCHAR ( MAX ), BlkBy VARCHAR ( MAX ), DBName VARCHAR ( MAX ), Command VARCHAR ( MAX ), CPUTime INT , DiskIO INT , LastBatch VARCHAR ( MAX ), ProgramName VARCHAR ( MAX ),...

I'm sure most programmers have run across a situation wherein they have to pass  a lot of parameters to a stored procedure call in C#. Instead of manually typing each parameter in and assigning a value, why not develop a way to automatically discover the parameter, add it to the parameter list of the command object and assign the corresponding value from an entity or model. Here's my implementation using extension method. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 /// <summary> /// Automatically assigns parameters to Command.Parameter Collection from List of parameter names /// </summary> /// <param name="that">Command object being extended</param> /// <param name="model">Expando model</param> /// <param name="conn">sQLConnection used to g...

I've been doing a lot of querying lately for a DB that I'm not that familiar, I would like to take note of helpful scripts I often used to analyze db structure. Find column name  1 2 3 4 5 6 7 SELECT t.name AS table_name , SCHEMA_NAME (schema_id) AS schema_name , c .name AS column_name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c .OBJECT_ID WHERE c .name LIKE '%EmployeeID%' ORDER BY schema_name , table_name ; Find table name  1 2 3 SELECT * FROM sys.Tables WHERE name LIKE '%Address%' Find SP name  1 2 3 select * from sys.procedures where name like '%name_of_proc%'

When I started doing T-SQL, I remember Cursors being demonized and is said to be Evil by most of my colleagues, this made me shun away from using or even trying to understand how it works. Finally, in one of the interviews  I was asked how would you retrieve hundred or maybe thousand records and iterate on each while validating each row and doing insert if a row is valid?  My quick answer was to simply create a Table variable containing all the columns needed, plus an indexer column, do a INSERT INTO SELECT statement and also use  the Row_Number() Over syntax. After inserting all the records to the Table variable I now iterate on each row by doing a while loop and declaring an counter and making increments as it goes to the loop, and using the incremented value as where clause in the select statement. This is how the code would look like.. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 DECLARE @TempTable Ta...

Yesterday I was asked what's the difference between UNION and UNION ALL , it made me think, I've been writing Stored Procedures for SQL Server for quite some time now. But almost always forget what what the difference between the two. It's time to reinforce it by writing it down through this blog. UNION  is used to combine result-sets of two or more SELECT statements,  the difference is really simple in UNION it only joins distinct values, meaning if there are duplicates it wont show it from the result-set, that's where ALL keyword is used when you want to include the duplicates in return. Union Syntax: SELECT ProductId FROM Products UNION SELECT  ProductId FROM Orders This will return unique  ProductId from Products and Orders Table combined and will ignore duplicates. Up Next differences between INNER JOIN, OUTER JOIN , LEFT and RIGHT JOIN and let's not forget CROSS JOIN CROSS JOIN Using cross join multiplies your rows from the first table to t...

What is Common Type Expressions? and How do we use it in our Stored Procedures? Common Type Expressions is almost similar to a view but you don't really create a physical view, instead you are querying a result set that is available for your SELECT, UPDATE, DELETE statements. Here's an example using Northwind: We may have a query like this: SELECT CustomerID,         CompanyName,         CONVERT(varchar,OrderDate,101) as OrderDate,         ProductName,         Quantity FROM (  SELECT  c.CustomerID, c.CompanyName,  o.OrderDate,                p.ProductName,  od.Quantity FROM Orders o INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID INNER JOIN dbo.[Order Details] od ON o.OrderID =  od.OrderID INNER JOIN dbo.Products p ON od.ProductID = p.ProductID ) details WHERE details.Quantity>10 With...