Database

PIEBALDconsult wrote:

That would be another reason not to use stored procedures

PIEBALDconsult wrote:

That would be another reason not to use
stored procedures. Things like that are so much simpler when the SQL is in your
DAL.

EXEC sp_executesql @DYNA_SQL, N'@NUM_ROWS int out'

System.Data.IDbConnection dbc = new System.Data.SqlClient.SqlConnection
(
    @"Server='localhost\SQLEXPRESS'; Database='Rubbish'; Trusted_Connection='True'"
) ;

System.Data.IDbCommand cmd = dbc.CreateCommand() ;

cmd.CommandText = "SELECT @voter_id=Id FROM Account WHERE " + "Name = 'Two'" ;

System.Data.IDbDataParameter prm = cmd.CreateParameter() ;

prm.ParameterName = "@voter_id" ;
prm.DbType = System.Data.DbType.Int32 ;
prm.Direction = System.Data.ParameterDirection.Output ;

cmd.Parameters.Add ( prm ) ;

dbc.Open() ;

cmd.ExecuteNonQuery() ;

int id = (int) prm.Value ;

cmd.Parameters.Clear() ;
cmd.CommandText = "SELECT Id FROM Account WHERE " + "Name = 'Three'" ;

object o = cmd.ExecuteScalar() ;

if ( o != System.DBNull.Value )
{
    id = (int) o ;
}

dbc.Close() ;

S Douglas wrote:

I've come to use stored procs for everything

PIEBALDconsult wrote:

They are rarely the right tool for the job.

public static T
GetAccountId<T>
(
    this System.Data.IDbConnection dbc
,
    string Filter
,
    params System.Tuple<string,object>[] Parameters
)
{
    T result = default(T) ;

    System.Data.IDbCommand cmd = dbc.CreateCommand() ;
    cmd.CommandText = "SELECT Id FROM Account WHERE " + Filter ;
    if ( Parameters != null )
    {
        foreach ( System.Tuple<string,object> p in Parameters )
        {
            System.Data.IDbDataParameter prm = cmd.CreateParameter() ;
            prm.ParameterName = p.Item1 ;
            prm.Value = p.Item2 ;
            cmd.Parameters.Add ( prm ) ;
        }
    }
    dbc.Open() ;
    object o = cmd.ExecuteScalar() ;
    if ( o != System.DBNull.Value )
    {
        result = (T) o ;
    }
    dbc.Close() ;
    return ( result ) ;
}

CREATE TABLE [dbo].[tblmaster](
	[testID] [varchar](50) NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[tblChild1](
	[testID] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Age] [int] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[tblChild2](
	[testID] [varchar](50) NULL,
	[Name] [varchar](50) NULL,
	[Address] [varchar](50) NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[tblChild3](
	[testID] [varchar](50) NULL,
	[Sex] [varchar](6) NULL,
	[Address] [varchar](50) NULL,
	[Phone Number] [varchar](50) NULL
) ON [PRIMARY]

insert into tblmaster
select 's401', 'name10' union all
select 's402', 'name11' union all
select 's403', 'name12' union all
select 's404', 'name14'

insert into tblChild1 
select 's401', 'name20', 20 union all
select 's402', 'name21', 21 union all
select 's403', 'name22', 22 union all
select 's404', 'name23',23

insert into tblChild2 
select 's401', 'name30', 'address30' union all
select 's402', 'name31', 'address31' union all
select 's403', 'name32', 'address32' union all
select 's404', 'name33', 'address33' 

insert into tblChild3 
select 's401', 'male', 'address40','9885446789' union all
select 's402', 'female', 'address41','12345678' union all
select 's403', 'male', 'address42','34567891' union all
select 's404', 'female', 'address43','0986234' 

Select * from tblmaster
Select * from tblChild1
Select * from tblChild2
Select * from tblChild3

exec sp_msforeachtable N'
  IF EXISTS(SELECT 1 FROM sys.columns WHERE object_id=OBJECT_ID(''?'') AND name = ''testID'')
    EXEC (''UPDATE ? SET testID= case when testID = ''''s401'''' then ''''I00010'''' 
				      when testID = ''''s402'''' then ''''I00020'''' 
				      when testID = ''''s403'''' then ''''I00030'''' 
				      when testID = ''''s404'''' then ''''I00040'''' 
                                  end'')'

Declare @Table Table
(
       EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select Abc,07/03/2010,6000,0 UNION ALL
Select xyz, 07/03/2011,1500,0 UNION ALL
Select Rameez, 07/03/2011,2000,0 UNION All
Select Abhijit, 07/03/2011,1000,0 UNION ALL

Declare @Table Table
(
    row int,    EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 1, 'Abc','07/03/2010',6000,0 UNION ALL
Select 2, 'xyz', '07/03/2011',1500,0 UNION ALL
Select 3, 'Rameez', '07/03/2011',2000,0 UNION All
Select 4, 'Abhijit', '07/03/2011',1000,0; --UNION ALL


 
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row )  RunningTotal 
from  @Table xx 

Declare @Table Table
(
    row int identity(1,1),    EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
(
EmployeeName
,joiningDate
,Amount
,Balance
)
Select   'Abc','07/03/2010',6000,0 UNION ALL
Select   'xyz', '07/03/2011',1500,0 UNION ALL
Select   'Rameez', '07/03/2011',2000,0 UNION All
Select   'Abhijit', '07/03/2011',1000,0; --UNION ALL


 
select xx.*
,(select sum(Amount) from @Table xy where xy.row <=xx.row )  RunningTotal 
from  @Table xx 

Declare @Table Table
(
       EmployeeName Varchar(50),joiningDate DateTime, Amount int,Balance int
)
insert into @Table
Select 'Abc','07/03/2010',6000,0 UNION ALL
Select 'xyz', '07/03/2011',1500,0 UNION ALL
Select 'Rameez', '07/03/2011',2000,0 UNION All
Select 'Abhijit', '07/03/2011',1000,0;  


with CTE01 as 
(select *
,ROW_NUMBER() over (order by joiningdate) row 
 from @Table )

select xx.*
,(select sum(Amount) from CTE01 xy where xy.row <= xx.row )  RunningTotal 
from  CTE01 xx