Creating Stored Procedure From C# environment

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Creating Stored Procedure From C# environment

Author

Topic

Duke
Starting Member

2 Posts

Posted - 2009-10-19 : 04:33:35

I have to create a Stored Procedure from SQL statements embedded in C# code. Anyone who can help. Code Below..
===================================================================
private String BaseQuery(string Level, string Selection, string DateType, string SpesificDate, string StartProdMonth , string EndProdMonth , string ProdCutOffDate , string StartDateRange , string EndDateRange)
{
string TheBaseQry = "" +
"Select * from (Select " +
"MPlan.Unit_1, " +
"0 Prodmonth, " +
" isnull(Sum(BusPlan.Squaremetres),0) as STP_BUSPLAN_SQM, " +
" isnull(Sum(STP_PLAN_SQM),0) as STP_PLAN_SQM, " +
" isnull(Sum(STP_PPLAN_SQM),0) as STP_PPLAN_SQM, " +
" isnull(Sum(STP_DPLAN_SQM),0) as STP_DPLAN_SQM, " +
" isnull(Sum(STP_PBOOK_SQM),0) as STP_PBOOK_SQM, " +
" isnull(Sum(STP_DBOOK_SQM),0) as STP_DBOOK_SQM, " +
" isnull(convert(Numeric(10),round(Sum(STP_PBOOK_SQM)/min(Shift.Shift)*Min(Shift.TotalShifts),0)),0) as STP_FCast_SQM , " +
" isnull(convert(Numeric(12,2),Sum(BusPlan.Grams)/1000),0) as STP_BUSPLAN_Kg, " +
" isnull(convert(Numeric(12,2),Sum(STP_PLAN_Grams)/1000),0) as STP_PLAN_Kg, " +
" isnull(convert(Numeric(12,2),Sum(STP_PPLAN_Grams)/1000),0) as STP_PPLAN_Kg, " +
" isnull(convert(Numeric(12,2),Sum(STP_DPLAN_Grams)/1000),0) as STP_DPLAN_Kg, " +
" isnull(convert(Numeric(12,2),Sum(STP_PBOOK_Grams)/1000),0) as STP_PBOOK_Kg, " +
" isnull(convert(Numeric(12,2),Sum(STP_DBOOK_Grams)/1000),0) as STP_DBOOK_Kg, " +
" isnull(convert(Numeric(12,2),round((Sum(STP_PBOOK_Grams)/min(Shift.Shift)*Min(Shift.TotalShifts))/1000,0)),0) as STP_FCast_Kg , " +
" isnull(convert(Numeric(12,2),Sum(BusPlan.Grams)),0) as STP_BUSPLAN_grams, " +
" isnull(convert(Numeric(12,2),Sum(STP_PLAN_Grams)),0) as STP_PLAN_grams, " +
" isnull(convert(Numeric(12,2),Sum(STP_PPLAN_Grams)),0) as STP_PPLAN_grams, " +
" isnull(convert(Numeric(12,2),Sum(STP_DPLAN_Grams)),0) as STP_DPLAN_grams, " +
" isnull(convert(Numeric(12,2),Sum(STP_PBOOK_Grams)),0) as STP_PBOOK_grams, " +
" isnull(convert(Numeric(12,2),Sum(STP_DBOOK_Grams)),0) as STP_DBOOK_grams, " +
" isnull(convert(Numeric(12,2),round((Sum(STP_PBOOK_Grams)/min(Shift.Shift)*Min(Shift.TotalShifts)),0)),0) as STP_FCast_grams , " +
" isnull(case when Sum(BusPlan.Squaremetres) = 0 then 0 else convert(Numeric(10),round((Sum(BusPlan.Grams)*100)/Sum(BusPlan.Squaremetres*2.70),0)) end,0) as STP_BUSPLAN_CMGT , " +
" isnull(case when Sum(STP_PLAN_SQMDens) = 0 then 0 else convert(Numeric(10),round((Sum(STP_PLAN_Grams)*100)/Sum(STP_PLAN_SQMDens),0)) end,0) as STP_PLAN_CMGT , " +
" isnull(case when Sum(STP_PPLAN_SQMDens) = 0 then 0 else convert(Numeric(10),round((Sum(STP_PPLAN_Grams)*100)/Sum(STP_PPLAN_SQMDens),0)) end,0) as STP_PPLAN_CMGT , " +
" isnull(case when Sum(STP_DPLAN_SQMDens) = 0 then 0 else convert(Numeric(10),round((Sum(STP_DPLAN_Grams)*100)/Sum(STP_DPLAN_SQMDens),0)) end,0) as STP_DPLAN_CMGT , " +
" isnull(case when Sum(STP_PBOOK_SQMDens) = 0 then 0 else convert(Numeric(10),round((Sum(STP_PBOOK_Grams)*100)/Sum(STP_PBOOK_SQMDens),0)) end,0) as STP_PBOOK_CMGT , " +
" isnull(case when Sum(STP_DBOOK_SQMDens) = 0 then 0 else convert(Numeric(10),round((Sum(STP_DBOOK_Grams)*100)/Sum(STP_DBOOK_SQMDens),0)) end,0) as STP_DBOOK_CMGT , " +
" isnull(case when Sum(STP_PPLAN_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_PPLAN_Grams)/Sum(STP_PPLAN_Tons),1)) end,0) as Prog_Gt , " +

" isnull(case when Sum(STP_PLAN_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_PLAN_Grams)/Sum(STP_PLAN_Tons),1)) end,0) as PLAN_Gt , " +
" isnull(case when Sum(STP_PPLAN_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_PPLAN_Grams)/Sum(STP_PPLAN_Tons),1)) end,0) as PPLAN_Gt , " +
" isnull(case when Sum(STP_DPLAN_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_DPLAN_Grams)/Sum(STP_DPLAN_Tons),1)) end,0) as DPLAN_Gt , " +
" isnull(case when Sum(STP_PBook_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_PBook_Grams)/Sum(STP_PBook_Tons),1)) end,0) as PBook_Gt , " +
" isnull(case when Sum(STP_DBook_Tons) = 0 then 0 else convert(Numeric(10,1),round(Sum(STP_DBook_Grams)/Sum(STP_DBook_Tons),1)) end,0) as DBook_Gt , " +

" isnull(min(Shift.Shift),0) as Shift, " +
" isnull(Min(Shift.TotalShifts),0) as TotalShifts " +
"from " +
"(Select PeerName_" + Level + " Unit_1, PeerName_2 Unit, " +
"a.Prodmonth, " +
"0 Activitycode, " +
"STP_PLAN_SQM = " +
"sum(a.squaremetres), " +
"STP_PLAN_Grams = " +
"sum(a.Grams), " +
"STP_PLAN_SQMDens = " +
"sum(a.squaremetres*d.Density), " +
"[STP_PLAN_TONS] = SUM(ONREEFTONS) " +
"from Planmonth a inner join sections_complete b on " +
"a.prodmonth = b.prodmonth and " +
"a.sectionid = b.sectionid " +
"inner join seccal z on " +
"z.prodmonth = b.prodmonth and " +
"z.sectionid = b.sectionid_1 " +
"inner join caltype x on " +
"z.CalendarTypeID = x.CalendarTypeID and " +
"z.Begindate <= x.calendardate and " +
"z.enddate >= x.calendardate " +
"inner join wp_density d on " +
"a.workplaceid = d.workplaceid ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + "where x.calendardate = '" + SpesificDate + "' ";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + "where a.prodmonth >= " + StartProdMonth + " and a.prodmonth <= " + EndProdMonth + "";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where x.calendardate >= '" + StartDateRange + "' and x.calendardate <= '" + EndDateRange + "' ";
break;
}

TheBaseQry = TheBaseQry + "and a.activitycode in (0,3) " +
"group by Peername_" + @Level + ", PeerName_2, a.Prodmonth) MPlan left join " +
"(Select PeerName_2 Unit, " +
"a.Prodmonth, " +
"0 Activitycode, " +
"STP_PPLAN_SQM = " +
"sum(a.squaremetres), " +
"STP_PPLAN_Grams = " +
"sum(a.Grams), " +
"STP_PPLAN_SQMDens = " +
"sum(a.squaremetres*d.Density), " +
"STP_PPLAN_Tons = " +
"sum(a.OnReefTons) " +
"from Planday a inner join sections_complete b on " +
"a.prodmonth = b.prodmonth and " +
"a.sectionid = b.sectionid " +
"inner join seccal z on " +
"z.prodmonth = b.prodmonth and " +
"z.sectionid = b.sectionid_1 " +
"inner join caltype x on " +
"z.CalendarTypeID = x.CalendarTypeID and " +
"z.Begindate <= x.calendardate and " +
"z.enddate >= x.calendardate " +
"inner join wp_density d on " +
"a.workplaceid = d.workplaceid ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + " where x.calendardate = '" + SpesificDate + "' and Tempdate <= '" + SpesificDate + "'";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + "where a.prodmonth >= " + StartProdMonth + " and a.prodmonth <= " + EndProdMonth + " and Tempdate <= '" + ProdCutOffDate + "'";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where Tempdate >= '" + StartDateRange + "' and Tempdate <= '" + EndDateRange + "' ";
break;

}

TheBaseQry = TheBaseQry + "and a.activitycode in (0,3) " +
"group by PeerName_2, a.Prodmonth) PPlan on " +
"MPlan.Prodmonth = PPlan.Prodmonth and " +
"MPlan.Unit = PPlan.Unit and " +
"MPlan.Activitycode = PPlan.Activitycode " +
"left join " +
"(Select PeerName_2 Unit, " +
"a.Prodmonth, " +
"0 Activitycode, " +
"STP_PBook_SQM = " +
"sum(a.squaremetres), " +
"STP_PBook_Grams = " +
"sum(a.Grams), " +
"STP_PBook_SQMDens = " +
"sum(a.squaremetres*d.Density), " +
"STP_PBook_Tons = " +
"sum(a.OnReefTons) " +
"from Book_stopingledging a inner join sections_complete b on " +
"a.prodmonth = b.prodmonth and " +
"a.sectionid = b.sectionid " +
"inner join seccal z on " +
"z.prodmonth = b.prodmonth and " +
"z.sectionid = b.sectionid_1 " +
"inner join caltype x on " +
"z.CalendarTypeID = x.CalendarTypeID and " +
"z.Begindate <= x.calendardate and " +
"z.enddate >= x.calendardate " +
"inner join wp_density d on " +
"a.workplaceid = d.workplaceid ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + " where x.calendardate = '" + SpesificDate + "' and Bookdate <= '" + SpesificDate + "' ";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + " where a.prodmonth >= " + StartProdMonth + " and a.prodmonth <= " + EndProdMonth + " and Bookdate <= '" + ProdCutOffDate + "' ";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where Bookdate >= '" + StartDateRange + "' and Bookdate <= '" + EndDateRange + "' ";
break;

}

TheBaseQry = TheBaseQry + " and a.activitycode in (0,3) " +
"group by PeerName_2, a.Prodmonth) SPBook on " +
"MPlan.Prodmonth = SPBook.Prodmonth and " +
"MPlan.Unit = SPBook.Unit and " +
"MPlan.Activitycode = SPBook.Activitycode " +
"left join " +
"(Select PeerName_2 Unit, " +
"a.Prodmonth, " +
"0 Activitycode, " +
"STP_DPLAN_SQM = " +
"sum(a.squaremetres), " +
"STP_DPLAN_Grams = " +
"sum(a.Grams), " +
"STP_DPLAN_SQMDens = " +
"sum(a.squaremetres*d.Density), " +
" STP_DPLAN_Tons = sum(OnreefTons) " +
"from Planday a inner join sections_complete b on " +
"a.prodmonth = b.prodmonth and " +
"a.sectionid = b.sectionid " +
"inner join seccal z on " +
"z.prodmonth = b.prodmonth and " +
"z.sectionid = b.sectionid_1 " +
"inner join caltype x on " +
"z.CalendarTypeID = x.CalendarTypeID and " +
"z.Begindate <= x.calendardate and " +
"z.enddate >= x.calendardate " +
"inner join wp_density d on " +
"a.workplaceid = d.workplaceid ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + " where x.calendardate = '" + SpesificDate + "' and Tempdate = '" + SpesificDate + "'";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + "where a.prodmonth >= " + StartProdMonth + " and a.prodmonth <= " + EndProdMonth + " and Tempdate = '" + ProdCutOffDate + "'";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where Tempdate >= '" + StartDateRange + "' and Tempdate <= '" + EndDateRange + "' ";
break;

}

TheBaseQry = TheBaseQry + " and a.activitycode in (0,3) " +
"group by PeerName_2, a.Prodmonth) DPlan on " +
"MPlan.Prodmonth = DPlan.Prodmonth and " +
"MPlan.Unit = DPlan.Unit and " +
"MPlan.Activitycode = DPlan.Activitycode " +
"left join " +
"(Select PeerName_2 Unit, " +
"a.Prodmonth, " +
"0 Activitycode, " +
"STP_DBook_SQM = " +
"sum(a.squaremetres), " +
"STP_DBOOK_Grams = " +
"sum(a.Grams), " +
"STP_DBOOK_SQMDens = " +
"sum(a.squaremetres*d.Density), " +
"STP_DBook_Tons = sum(OnreefTons) " +
"from Book_stopingledging a inner join sections_complete b on " +
"a.prodmonth = b.prodmonth and " +
"a.sectionid = b.sectionid " +
"inner join seccal z on " +
"z.prodmonth = b.prodmonth and " +
"z.sectionid = b.sectionid_1 " +
"inner join caltype x on " +
"z.CalendarTypeID = x.CalendarTypeID and " +
"z.Begindate <= x.calendardate and " +
"z.enddate >= x.calendardate " +
"inner join wp_density d on " +
"a.workplaceid = d.workplaceid ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + " where x.calendardate = '" + SpesificDate + "' and Bookdate = '" + SpesificDate + "' ";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + " where a.prodmonth >= " + StartProdMonth + " and a.prodmonth <= " + EndProdMonth + " and Bookdate = '" + ProdCutOffDate + "'";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where Bookdate >= '" + StartDateRange + "' and Bookdate <= '" + EndDateRange + "' ";
break;

}

TheBaseQry = TheBaseQry + " and a.activitycode in (0,3) " +
"group by PeerName_2, a.Prodmonth) SDBook on " +
"MPlan.Prodmonth = SDBook.Prodmonth and " +
"MPlan.Unit = SDBook.Unit and " +
"MPlan.Activitycode = SDBook.Activitycode " +
"inner join " +
"(select a.Prodmonth, PeerName_2 Unit, " +
"Avg(Shift) Shift, " +
"convert(int,Avg(TotalShifts)) TotalShifts " +
"from (select b.prodmonth, Peername, reporttoID, " +
"Count(b.prodmonth) Shift, " +
"Avg(TotalShifts) TotalShifts " +
"from " +
"sections b " +
"inner join seccal c on " +
"b.prodmonth = c.prodmonth and " +
"b.sectionid = c.sectionid " +
"inner join caltype d on " +
"c.calendartypeid = d.calendartypeid and " +
"c.begindate <= d.Calendardate and " +
"c.enddate >= d.calendardate ";

switch (_DateType)
{
case "Spesific":
TheBaseQry = TheBaseQry + " where d.calendardate >= convert(datetime, '" + SpesificDate + "')-45 and d.calendardate <= '" + SpesificDate + "' ";
break;
case "ProdMonth":
TheBaseQry = TheBaseQry + " where d.calendardate >= convert(datetime, '" + ProdCutOffDate + "')-45 and d.calendardate <= '" + ProdCutOffDate + "' ";
break;
case "DateRange":
TheBaseQry = TheBaseQry + "where d.calendardate >= '" + StartDateRange + "' and d.calendardate <= '" + EndDateRange + "' ";
break;
}

TheBaseQry = TheBaseQry + "and d.workingday = 'Y' " +
"group by b.prodmonth, Peername, reporttoID) a inner join " +
"sections_from_sb b on " +
"a.prodmonth = b.prodmonth and " +
"a.reporttoID = b.sectionid_2 " +
"group by a.Prodmonth, PeerName_2) Shift on " +
"MPlan.Prodmonth = Shift.Prodmonth and " +
"MPlan.Unit = Shift.Unit " +
"left outer join Stoping_Business_Plan_MO BusPlan on " +
"MPlan.Prodmonth = BusPlan.Prodmonth and " +
"MPlan.Unit = BusPlan.PeerName_2 " +
"group by mplan.Unit_1) as DashboardBaseTable " +
" where unit_1 = '" + Selection + "'";

return TheBaseQry;

}

Kind Regards
David Morakong
dukesta@gmail.com

Duke
Starting Member

2 Posts

Posted - 2009-10-20 : 03:35:39

Is there anyone who has a suggestion or comment on the above function? Do i need to simplify it in any way?

Kind Regards
David Morakong
dukesta@gmail.com

rammohan
Posting Yak Master

212 Posts

Posted - 2009-11-02 : 02:28:44

u want CLR stored procedure?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources