sir
i want to create temporary table and insert data through store procedure and query
<pre>Alter PROCEDURE dbo.sp_ExpenMonthWise
(
@schemeno char(20),
@sch_amount nvarchar(max),
@Desciption nvarchar(2000),
@sdate smalldatetime,
@Exp_upto_Premonth nvarchar(max),
@Exp_Upto nvarchar(max),
@Exp_During nvarchar(max),
@ExpUpto nvarchar(max),
@PreMonth varchar(50),
@CurrMonth varchar(50)
)
AS
BEGIN
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
CREATE TABLE #Temp
(
TempID INT IDENTITY(1,1) PRIMARY KEY, schemeno char(20), sch_amount nvarchar(max),Desciption nvarchar(2000),sdate smalldatetime,Exp_upto_Premonth nvarchar(max),
Exp_Upto nvarchar(max),Exp_During nvarchar(max),ExpUpto nvarchar(max),PreMonth Varchar(50),CurrMonth Varchar(50)
);
INSERT INTO #Temp(schemeno,sch_amount,Desciption,sdate,Exp_upto_Premonth,Exp_Upto,Exp_During,ExpUpto,PreMonth,CurrMonth) VALUES
(@schemeno,@sch_amount,@Desciption,@sdate,@Exp_upto_Premonth,@Exp_Upto,@Exp_During,@ExpUpto,@PreMonth,@CurrMonth)
DECLARE @totalRecords INT
DECLARE @I INT
SELECT @I = 1
SELECT @totalRecords = COUNT(TempID) FROM #Temp
WHILE (@I <= @totalRecords)
BEGIN
SELECT * FROM #Temp WHERE TempID = @I
SELECT @I = @I + 1
END
SELECT schemeno, sch_amount, Desciption, sdate, Exp_upto_Premonth,Exp_Upto,Exp_During,ExpUpto,PreMonth,CurrMonth
FROM #Temp
END;
Insert code through loop:-
foreach (DataTable table in Insrtds.Tables)
{
foreach (DataRow row in table.Rows)
{
SqlCommand cmd = new SqlCommand("dbo.sp_ExpenMonthWise", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@schemeno", row["schemeno"]);
cmd.Parameters.AddWithValue("@sch_amount", row["sch_amount"]);
cmd.Parameters.AddWithValue("@Desciption", row["disp"]);
cmd.Parameters.AddWithValue("@sdate", row["schdate"]);
cmd.Parameters.AddWithValue("@Exp_upto_Premonth", row["Premonth"]);
cmd.Parameters.AddWithValue("@Exp_upto", row["ExpUpto"]);
cmd.Parameters.AddWithValue("@Exp_During", row["CurrMonExp"]);
cmd.Parameters.AddWithValue("@ExpUpto", row["ExpUptoNow"]);
cmd.Parameters.AddWithValue("@PreMonth", PreMonthName);
cmd.Parameters.AddWithValue("@CurrMonth", CurrMonthName);
cmd.ExecuteNonQuery();
What I have tried:
i want to create temporary table and insert data