September 23, 2005 at 6:15 pm
Hi,
can anyone tell me a better way to do the following:-
set @sql=''
set @sql='select @BusRatio=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_Ratio'''
Print(@sql)
Exec sp_executesql @sql,N'@BusRatio varchar(512) OUTPUT', @BusRatio OUTPUT
set @sql=''
set @sql='select @busFreq=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''FSB_Frequency'''
Print(@sql)
Exec sp_executesql @sql,N'@busFreq varchar(512) OUTPUT', @busFreq OUTPUT
set @sql=''
set @sql='select @CoreFrequency=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Node0_Core_Fequency'''
Print(@sql)
Exec sp_executesql @sql,N'@CoreFrequency varchar(512) OUTPUT', @CoreFrequency OUTPUT
set @sql=''
set @sql='select @Number_of_Cores_per_Package=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Number_of_Cores_per_Package'''
Print(@sql)
Exec sp_executesql @sql,N'@Number_of_Cores_per_Package varchar(512) OUTPUT', @Number_of_Cores_per_Package OUTPUT
set @sql=''
set @sql='select @Number_of_Package=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Number_of_Package'''
Print(@sql)
Exec sp_executesql @sql,N'@Number_of_Package varchar(512) OUTPUT', @Number_of_Package OUTPUT
set @sql=''
set @sql='select @Number_of_Threads_per_Core=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Number_of_Threads_per_Core'''
Print(@sql)
Exec sp_executesql @sql,N'@Number_of_Threads_per_Core varchar(512) OUTPUT', @Number_of_Threads_per_Core OUTPUT
set @sql=''
set @sql='select @SNC0_PK0_cache_size=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK0_cache_size'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK0_cache_size varchar(512) OUTPUT', @SNC0_PK0_cache_size OUTPUT
set @sql=''
set @sql='select @SNC0_PK0_CPU_type=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK0_CPU_type'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK0_CPU_type varchar(512) OUTPUT', @SNC0_PK0_CPU_type OUTPUT
set @sql=''
set @sql='select @SNC0_PK0_stepping=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK0_stepping'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK0_stepping varchar(512) OUTPUT', @SNC0_PK0_stepping OUTPUT
set @sql=''
set @sql='select @SNC0_PK1_cache_size=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK1_cache_size'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK1_cache_size varchar(512) OUTPUT', @SNC0_PK1_cache_size OUTPUT
set @sql=''
set @sql='select @SNC0_PK1_CPU_type=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK1_CPU_type'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK1_CPU_type varchar(512) OUTPUT', @SNC0_PK1_CPU_type OUTPUT
set @sql=''
set @sql='select @SNC0_PK1_stepping=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK1_stepping'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK1_stepping varchar(512) OUTPUT', @SNC0_PK1_stepping OUTPUT
set @sql=''
set @sql='select @SNC0_PK2_cache_size=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK2_cache_size'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK2_cache_size varchar(512) OUTPUT', @SNC0_PK2_cache_size OUTPUT
set @sql=''
set @sql='select @SNC0_PK2_CPU_type=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK2_CPU_type'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK2_CPU_type varchar(512) OUTPUT', @SNC0_PK2_CPU_type OUTPUT
set @sql=''
set @sql='select @SNC0_PK2_stepping=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK2_stepping'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK2_stepping varchar(512) OUTPUT', @SNC0_PK2_stepping OUTPUT
set @sql=''
set @sql='select @SNC0_PK3_cache_size=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK3_cache_size'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK3_cache_size varchar(512) OUTPUT', @SNC0_PK3_cache_size OUTPUT
set @sql=''
set @sql='select @SNC0_PK3_CPU_type=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK3_CPU_type'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK3_CPU_type varchar(512) OUTPUT', @SNC0_PK3_CPU_type OUTPUT
set @sql=''
set @sql='select @SNC0_PK3_stepping=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''SNC0_PK3_stepping'''
Print(@sql)
Exec sp_executesql @sql,N'@SNC0_PK3_stepping varchar(512) OUTPUT', @SNC0_PK3_stepping OUTPUT
set @sql=''
set @sql='select @Total_NumProc_Installed=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Total_NumProc_Installed'''
Print(@sql)
Exec sp_executesql @sql,N'@Total_NumProc_Installed varchar(512) OUTPUT', @Total_NumProc_Installed OUTPUT
set @sql=''
set @sql='select @Total_Packages=configvalue from [EPVSQL03].[sv_seeds].[dbo].['+@SeedTableName+ '] s inner join [EPVSQL03].[sv_seeds].[dbo].['+@TestExecutionTableName +'] t on
s.ID = t.ID where t.seedName='''+@seedName +''' and configName =''Total_Packages'''
Print(@sql)
Exec sp_executesql @sql,N'@Total_Packages varchar(512) OUTPUT', @Total_Packages OUTPUT
--Exec('SET ANSI_NULLS ON SET ANSI_WARNINGS ON ' +@sql)
Select @BusRatio as BusRatio
update #tbl_seed_info1
set [BusRatio] =@BusRatio,
[busFreq]=@busFreq,
[CoreFrequency]=@CoreFrequency,
[Number_of_Cores_per_Package]=@Number_of_Cores_per_Package,
[Number_of_Package]=@Number_of_Package,
[Number_of_Threads_per_Core]=@Number_of_Threads_per_Core,
[SNC0_PK0_cache_size] =@SNC0_PK0_cache_size,
[SNC0_PK0_CPU_type] =@SNC0_PK0_CPU_type,
[SNC0_PK0_stepping] =@SNC0_PK0_stepping,
[SNC0_PK1_cache_size]=@SNC0_PK1_cache_size,
[SNC0_PK1_CPU_type]=@SNC0_PK1_CPU_type,
[SNC0_PK1_stepping]=@SNC0_PK1_stepping
where seedName=@seedName
update #tbl_seed_info2
set [SNC0_PK2_cache_size] =@SNC0_PK2_cache_size,
[SNC0_PK2_CPU_type] =@SNC0_PK2_CPU_type,
[SNC0_PK2_stepping] =@SNC0_PK2_stepping,
[SNC0_PK3_cache_size]=@SNC0_PK3_cache_size,
[SNC0_PK3_CPU_type]=@SNC0_PK3_CPU_type,
[SNC0_PK3_stepping]=@SNC0_PK3_stepping,
[Total_NumProc_Installed] =@Total_NumProc_Installed,
[Total_Packages] =@Total_Packages
where seedName=@seedName
Thanks,
ssm
SSM
September 24, 2005 at 6:06 am
So @SeedTableName,@TestExecutionTableName are variable tables?
September 25, 2005 at 12:29 am
yes,these are table names.Basically these queries are run across many such seedTable,testtable pairs.
I get configvalues from these tables based on certain configname values and update into a temporary table which is my resultant dataset.
thanks,
ssm
SSM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply