USE [csoft]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Search] @prd varchar(2),
@rep varchar(2),
@yy varchar(4),
@mm varchar(2)
AS
DECLARE @VTEXT VARCHAR(8000)
DECLARE @SNO VARCHAR(4)
Declare @SQL VarChar(8000)
DECLARE @Q VARCHAR(2)
declare @num varchar(10)
DECLARE db_cursor CURSOR FOR
SELECT sno, view_text
FROM month_scripts2
where prd_code = @prd
and rep_code = @rep
set @num=0
SET @Q = ''''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @SNO,@VTEXT
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = @VTEXT
SET @SQL = REPLACE(@SQL,'''MM''',@Q+@MM+@Q)
SET @SQL = REPLACE(@SQL,'''YYYY''',@Q+@YY+@Q)
PRINT @sno + ' - ' + @SQL
Exec ( @SQL)
FETCH NEXT FROM db_cursor INTO @SNO,@VTEXT
END
CLOSE DB_CURSOR
DEALLOCATE DB_CURSOR
Code for Showing Data in Data Grid as follows
SqlConnection conn = new SqlConnection(@"Data Source=SHARIQUE\SQLEXPRESS;Initial Catalog=csoft;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Search";
cmd.Parameters.Add("@prd", SqlDbType.VarChar, 50).Value = "11";
cmd.Parameters.Add("@rep", SqlDbType.VarChar, 50).Value = "31";
cmd.Parameters.Add("@yy", SqlDbType.VarChar, 50).Value = "2008";
cmd.Parameters.Add("@mm", SqlDbType.VarChar, 50).Value = "04";
SqlDataAdapter da = new SqlDataAdapter(cmd);
conn.Open();
DataSet ds = new DataSet();
da.Fill(ds);
foreach (DataRow dr in ds.Tables[0].Rows)
{
foreach (DataColumn dc in ds.Tables[0].Columns)
{
dataGridView1.DataSource = ds.Tables[0].DefaultView;
conn.Close();
}
}
}