Create Dynamic SQLs via Stored Procedure

If you’re a developer, irrespective of the platform, you would have to work with Databases. Creating SQL statements for tables is quite often a monotonous job and it gets hectic especially with dealing gigantic tables that have hundreds of columns.

Writing SQL statements manually every time becomes a tiresome process. But we have a solution. You could write a Stored Procedure to automatically generate the queries. We have attached the Stored Procedure code for MSSQL Server, though you can replicate to any database with minor changes.

CREATE proc [dbo].[USP_QuerycreationSupport]
(
@table_Name varchar(100)

)
as
begin
DECLARE @InserCols NVARCHAR(MAX)
DECLARE @Inserparam NVARCHAR(MAX)
DECLARE @Insertquery NVARCHAR(MAX)
DECLARE @Selectquery NVARCHAR(MAX)
DECLARE @Update NVARCHAR(MAX)
DECLARE @DeleteQuery NVARCHAR(MAX)

 -- sp param

 SELECT 
    '@'+c.name+ SPACE(1) + case cast(t.Name as nvarchar(40))   when 'nvarchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')' 
                  when 'varchar'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'char'    then t.Name+'('+cast(c.max_length as nvarchar(30))+')'
                  when 'decimal' then t.Name+'(18,2)' else t.Name end +'=null,' as colss
   
   
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID(@table_Name)




 select 'Insert query'
 SET @InserCols=  ( SELECT DISTINCT (SELECT   sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @InserCols=LEFT(@InserCols,LEN(@InserCols)-1)
 --select @InserCols

 
 SET @Inserparam=  ( SELECT DISTINCT (SELECT   '@'+sc.NAME +',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Inserparam=LEFT(@Inserparam,LEN(@Inserparam)-1)
 --select @Inserparam

 set @Insertquery='insert into '+@table_Name+'('+@InserCols+')'+'values'+'('+@Inserparam+')'
 select @Insertquery

 select 'Update Query'
 SET @Update=  ( SELECT DISTINCT (SELECT   sc.NAME +'=@'+sc.NAME+',' FROM
           sys.tables st INNER JOIN sys.columns sc ON st.object_id = sc.object_id
                                   WHERE st.name = @table_Name
           FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'))

 -- Return the result of the function
 SELECT @Update=LEFT(@Update,LEN(@Update)-1)
 --select @Update
 SET @Update='UPdate  '+@table_Name+'  set '+@Update
 select @Update

 -- For select Query
 select 'Select Query'
  
 set @Selectquery='select '+@InserCols +' from '+ @table_Name
 select @Selectquery

 -- For Delete Query
 select 'Delete Query'
  
 set @DeleteQuery='delete from  '+ @table_Name
 select @DeleteQuery
end

 --   exec USP_QuerycreationSupport @table_Name='MST_ComboMain'
 

Step 1: Create the StoredProcedure. The attached file contains the code for creating a Stored Procedure that auto-generates SQL Queries.

Step 2: Execute the StoredProcedure, passing your required table name as a parameter.

execUSP_QuerycreationSupport@table_Name=’mstCustomer’

List Example with Keyvaluepair

KeyValuePair

var myList = new List<KeyValuePair<int, string>>();
myList.Add(new KeyValuePair<int, string>(1, "One");

foreach (var item in myList)
{
    int i = item.Key;
    string s = item.Value;
}
or if you are .NET Framework 4 you could use:

var myList = new List<Tuple<int, string>>();
myList.Add(Tuple.Create(1, "One"));

foreach (var item in myList)
{
    int i = item.Item1;
    string s = item.Item2;
}
If either the string or integer is going to be unique to the set, you could use:

Dictionary<int, string> or Dictionary<string, int>

Difference between a stack, a queue and a heap

A stack keeps track of what is executing and contains stored value types to be accessed and processed as Last In First Out, with elements both inserted and deleted from the top end.

A queue lists items on a First In First Out basis in terms of both insertion and deletion, with the developer inserting items from the rear end and deleting items from the front end of the queue.

A heap contains stored reference types and is responsible for keeping track of more precise objects.

Read-only variables VS Constants

1. Read-only variables can support reference-type variables. Constants can hold only value-type variables.

2. Developers evaluate read-only variables at the runtime. They evaluate constants at the compile time.

LINQ

LINQ stands for Language Integrated Query.

Language-Integrated Query (LINQ) is an innovation introduced in Visual Studio 2008 and .NET Framework version 3.5 that bridges the gap between the world of objects and the world of data.

You can use LINQ queries in new projects, or alongside non-LINQ queries in existing projects. The only requirement is that the project target .NET Framework 3.5 or later.

This is a Microsoft programming model and methodology that offers developers a way to manipulate data using a succinct yet expressive syntax. It does so by instilling Microsoft .NET-based programming languages with the ability to make formal queries

In Visual Studio you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable<T> interface. LINQ support for the ADO.NET Entity Framework is also planned, and LINQ providers are being written by third parties for many Web services and other database implementations.