---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-13 17:00:06 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] varchar(1),[C] int) insert [tb] select 1,'*',3 --------------开始查询-------------------------- declare @sql varchar(8000)set @sql=''select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tbset @sql=left(@sql,len(@sql)-10)exec(@sql) ----------------结果---------------------------- /* 3 */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2014-06-13 17:00:06 -- Version: -- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) -- Feb 10 2012 19:13:17 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([A] int,[B] varchar(1),[C] int) insert [tb] select 1,'*',3 union all select 2,'/',2 --------------开始查询-------------------------- declare @sql varchar(8000)set @sql=''select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tbset @sql=left(@sql,len(@sql)-10)exec(@sql) ----------------结果---------------------------- /* col ----------- 3 1(2 行受影响) */
---------------------------------------------------------------- -- Author :DBA_HuangZJ(发粪涂墙) -- Date :2014-06-13 16:57:47 -- Version: -- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) -- Apr 2 2010 15:48:46 -- Copyright (c) Microsoft Corporation -- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据[TB] if object_id('[TB]') is not null drop table [TB] go create table [TB]([A] int,[B] nvarchar(2),[C] int) insert [TB] select 1,'*',3 --------------生成数据-------------------------- DECLARE @a VARCHAR(10) SELECT @a= 'select '+CAST(a AS VARCHAR)+CAST(b AS VARCHAR)+CAST(c AS VARCHAR)+''from [TB] exec (@a)----------------结果---------------------------- /* ----------- 3 */
create table t (A varchar(5),B varchar(5),C varchar(5))insert into t select '1','*','3' union all select '8','/','4' union all select '3','+','5' union all select '5','-','2' declare @tsql varchar(6000)select @tsql=isnull(@tsql+' union all','') +' select '''+A+B+C+'''+''=''+rtrim('+A+B+C+') ''result'' ' from t exec(@tsql)/* result ---------------- 1*3=3 8/4=2 3+5=8 5-2=3(4 row(s) affected) */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-13 17:00:06
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] varchar(1),[C] int)
insert [tb]
select 1,'*',3
--------------开始查询--------------------------
declare @sql varchar(8000)set @sql=''select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tbset @sql=left(@sql,len(@sql)-10)exec(@sql)
----------------结果----------------------------
/*
3
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-06-13 17:00:06
-- Version:
-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
-- Feb 10 2012 19:13:17
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] varchar(1),[C] int)
insert [tb]
select 1,'*',3 union all
select 2,'/',2
--------------开始查询--------------------------
declare @sql varchar(8000)set @sql=''select @sql=@sql+'select col='+ltrim(A)+''+B+''+ltrim(C)+' union all ' from tbset @sql=left(@sql,len(@sql)-10)exec(@sql)
----------------结果----------------------------
/*
col
-----------
3
1(2 行受影响)
*/
-- Author :DBA_HuangZJ(发粪涂墙)
-- Date :2014-06-13 16:57:47
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
-- Apr 2 2010 15:48:46
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据[TB]
if object_id('[TB]') is not null drop table [TB]
go
create table [TB]([A] int,[B] nvarchar(2),[C] int)
insert [TB]
select 1,'*',3
--------------生成数据--------------------------
DECLARE @a VARCHAR(10)
SELECT @a= 'select '+CAST(a AS VARCHAR)+CAST(b AS VARCHAR)+CAST(c AS VARCHAR)+''from [TB]
exec (@a)----------------结果----------------------------
/*
-----------
3
*/
create table t
(A varchar(5),B varchar(5),C varchar(5))insert into t
select '1','*','3' union all
select '8','/','4' union all
select '3','+','5' union all
select '5','-','2'
declare @tsql varchar(6000)select @tsql=isnull(@tsql+' union all','')
+' select '''+A+B+C+'''+''=''+rtrim('+A+B+C+') ''result'' '
from t exec(@tsql)/*
result
----------------
1*3=3
8/4=2
3+5=8
5-2=3(4 row(s) affected)
*/