Database

velmahesh wrote:

Please,Dont Forget to click   Accept Answer and Click vote.. Earn
Points.
 

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 ) ;
}