Passing Multiple and Integer Values to Dynamic Query in SQL

In this Post I am going to explain how to filter data in dynamic query in this particular case dynamic pivoting using int variables.

Here is the stored procedure with variables as filters for dynamic query.

Create PROCEDURE skhpReportVillagewisemonthly
    @Fiscalyear int=null,
    @level int =null
AS
BEGIN
Declare @Columns nvarchar(max)
Declare @SQL nvarchar(max)
select @Columns=
COALESCE(@Columns+', ','')+QUOTENAME (VillageID)
from (select VillageID from skhpvillage
) as M
Order By M.VillageID

Set @SQL='
with pivotdata as
(
select VillageID,
Monthid,ActualUnit
from skhpviewDemandBillDetail
where levelid=@level
)

select Monthid,'+@Columns+' from PivotData
Pivot
(
SUM(ActualUnit) for VillageID in (' + @Columns + ')
) as pivotresult
'
exec sp_ExecuteSQL @SQL, N'@level int,@Fiscalyear int', @level,@Fiscalyear
END
GO


In the above example
  @Fiscalyear int=null,
    @level int =null 
are the variable for the data filter in Dynamic Query.

Here is the code where Filter is used using the variable.
Set @SQL='
with pivotdata as
(
select VillageID,
Monthid,ActualUnit
from skhpviewDemandBillDetail
where levelid=@level
)


To Pass the variable to the dynamic query we use exec sp_ExecuteSQL  where we can define variables and send the values as below.
exec sp_ExecuteSQL @SQL, N'@level int,@Fiscalyear int', @level,@Fiscalyear

Keep reading my blog for more such queries and feel free to leave comments. 
Gautam Kumar Gupta

Integrating different modules of software challenges.

Single modules are easy and less prone to errors. Integration of related modules may be a much breath taking job. Every thing works fine when separate but on integration there are hundreds of things to take care of.
What is the basis of integration of integration?
: you need to find the exact star point for both the modules you are going to integrate so that some ids should be unique for entry point of one system to another.

why to integrate?
: single solution is emerging demand among customers basically for office automation systems. So that every thing is in one place and related to all other component and hence tracking process and resource becomes easier.

How much to integrate?
: extent of integration may depend on the requirement of the customer and decision of developer. But few things to be considered are
* system should not loose its dynamic nature.
* both system should be complete independently, although this could be alittle difficult to accomplish in few cases but the focus should be on making independent as much as possible.

What are Mostly demanded integrated solution?
: mostly
 billing and account.
Inventort and tracking system.
MIS and website.
Production and inventort.
Attendance and payroll.

Erp are major market holders for integrated solutions around the globe.

Integration Mechanism:

 There Could Be Many Integration Mechanism but I am Going to enlist few of my personally developed mechanism which are tested and are working for me and hope will hope to help others with some integrated MIS Development.

1. Using Union in View.
2. Using Bridge Table to and Integration Rule.

Using Union in View.
This is an easier mechanism but may not be useful in all cases.
In this mechanism the table structure should be similar or is made similar in view while Union.
There is not much overhead of changing reports from the data in this mechanism as the same view is used only the data it retrieves increases with the union of different tables.
useful in case of sales, purchase, journal, payment etc vouchers.

To break the Integration of this type of Integration, view needs to be changed.
May need extra effort for user level customization(To add or Integration feature by user).

When integrated using this mechanism no extra work for user i.e. if  inventory vouchers are integrated to account using this method there is no need to regenerate the voucher in account module. the view automatically gets data from inventory module needed for account.

Note.: If Possible Use this method (Best for usability).

Using Bridge Table to and Integration Rule.
In this method we use bridge tables to get the equivalent data structure between tables  different modules.
Then we write integration rule ie what to get from which column of what table for the voucher of account module from inventory module.

In this method different interface gets data from inventory module on the basis of integration rule using bridge tables and then user varifies the data and approves the voucher.

This case is needed where different independent bodies need to share there information to one another via application.

For example: Loan section and Account section of an organization are independent bodies then issue of loan should directly effect the account but should only using different voucher in account but what if all the data will be re posted manually by the user that will be a tidious joub in such case this kind on integration is very usefull as it will retrieve the data from other module as it is in the module it need to be integrated with and user will only have to approve the data.

software implementation challenges.

Completing a software project may feel lot easier some time than implementing them. This case is very much true if the case is of client with low it knowledge or users are of the age of  your father or even grand fathers. In some government clients i personally got frustated due to their resistance for the change in their working culture as earlier using papers for every task was seem alor easier for them than this alien concept of having no paper.
"I am going to retire in few months why should i learn this new software." One of my user said to me took me for breakfast leaving the work table. What can you answer to that grandpa?
In other cases the users are against that you have been assigned for the project not one of his previous dealer or relative, so he is total non cooprrative and will give as low detail as possible initially and after generating out put he will say that your output is wrong. You go for every details minutely again and find that he didn't  informed you of some details. You get frustated but still you re do all the task. But you wont get the correct report yet because he is still hiding some detail and this process goes for month untill manager gets frustated and replaces the coordinator or closes the project. In any case your reputation is going down.

career as asp .net developer and challenges.

Every career has its challenges and upportunities, same is with asp .net developers. Their is always new task to accomplish and their challanges are uncertain.
Not every ideas gets accomplished as there is always client delivery pressure and deadlines to meet.
Projects are bigger and complex. Knowledge required are many to sustain in the field. Few most important are jquery, css, html, xml, json, sql, mvc, c#, webservices, javascript. Mastering all is other challenging part of the career nut yet we need to go through all of this. So that any of these can be used when needed and is needed most of the time.
Major tasks as developer in asp .net includes mis development for many domains.
Majority of industry are dependent on information and mis is the most for all to compete. These infomation system are now vastly migrating to web technologies and asp is one of the top choises of the development because of its vast library and support.
Real time systems is also possible now due to signalr with the release of .net frame work 4.5 and hence have increasing the scope of the technology.
This trend of enhancement in .net technology in every now and then also have made the market of asp .net development more preferable.
But any break in development career may that be of few months can make you get going from the industry as practice and knowledge required to be continuous in this industry to sustain the competition. Be that be in full time job or in freelancing. Although freelancing has not as much upportunities as it provides for php developers.
Problem solving skill and on site solution finding is another challenge for developera as there may be bug even after delivery and also after all the testings. On site solution provided may be temporary for the system to run instantly but you need to find a permanent solution for such problems or bugs and deliver them to customer as their working and business depends on this.

Loop in T-sql using Temporary Table and while loop equivalent to Foreach loop.

This post will explain you the process of performing Foreach equivalent loop using Temporary Table and while loop iteration through the temporary table.
Usage:
1. To achieve loop without use of cursor.
2. To achieve Foreach like functionality i.e. can get current row in the loop.
3. Insert data in some table in bulk for no of fixed range like in case of fixed customer billing, Student bill assign.
First You need to create a temporary table with auto increment integer as primary key and second column for the data you are going to check loop on.
 ex.

CREATE TABLE #temp(
uniqueid smallint Primary Key IDENTITY(1,1)
 ,col1       INT
);

Now we can insert data in the temporary table trough which we are going to traverse.

insert into #temp (col1  )
select customerid from skhpcustomer;

After having data in temporary table now we are all set to go for a loop for which we can set an integer flag to get current row.

declare @i int=1;
while(@i<=(select max(uniqueid) from #temp))
begin
--Here Other task on data can be performed by getting col1 for uniqueid in #temp
print @i;

set @i=@i+1;
end

Drop table #temp -- Donot forget to clear un wanted temp data.

This completes the loop which is equivalent to foreach loop required for different condition in SQL Development.