Database

Member 8003276 wrote:

ADD PRIMARY KEY (BatchID,StartTime)
 

ALTER TABLE BatchData
ADD CONSTRAINT PK_BatchData PRIMARY KEY (BatchID, StartTime);

SET @Command = 'DECLARE @voter_id INT SELECT @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query
EXEC (@Command)

create table #Value (
val int 
)

set @command = 'insert into #value (val) SELECT top 1 voterid FROM tblCamp_CT WHERE ' +@Query

 exec(@command) 

set @voter_id=(select top 1 val from #Value)
delete #Value  

declare @voter_id int , @query varchar(max), @command nvarchar(4000) 
,@parm nvarchar(20) 
 
set @query='1=1'

SET @Command = 'SELECT top 1 @voter_id = voterid FROM tblCamp_CT WHERE ' + @Query
 
 
set @parm ='@voter_id int output '

  exec sp_executesql @command,@parm ,@voter_id out    
 
 select  @voter_id test 

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'')'