SQL code动态sql语句基本语法
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
1 :普通SQL语句可以用Exec执行 eg: Select * from tableName
Exec('select * from tableName')
Exec sp_executesql N'select * from tableName' -- 请注意字符串前一定要加N 2:字段名,表名,数据库名之类作为变量时,必须用动态SQL eg:
declare @fname varchar(20)
set @fname = 'FiledName'
Select @fname from tableName -- 错误,不会提示错误,但结果为固定值FiledName,并非所要。
Exec('select ' + @fname + ' from tableName') -- 请注意 加号前后的 单引号的边上加空格 当然将字符串改成变量的形式也可
declare @fname varchar(20)
set @fname = 'FiledName' --设置字段名 declare @s varchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000)
set @s = 'select ' + @fname + ' from tableName'
Exec(@s) -- 成功
exec sp_executesql @s -- 此句正确 3. 输出参数
declare @num int,
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
SELECT XXX,XXX
FROM (
SELECT XXX,XXX,XXX,XXX,XXX
FROM XXX
) AS A
JOIN #T AS B
ON A.ID=B.ID--you know?
GO
create table tb2 (id2 int)
GO
insert into tb1(id1)
select 1 union all
select 2 union all
select 3
insert into tb2 (id2)
select 1 union all
select 3declare @strSQL varchar(1000);
set @strSQL='SELECT ID2 FROM tb2';
--重写一个新的语句字符串,包含原子句,再整体exec
declare @strSQL2 varchar(2000);
set @strSQL2 ='select id1 from tb1 where id1 in ('+@strSQL+')'
exec (@strSQL2 )id1
-----------
1
3(2 行受影响)
单独EXEC('SELECT ID--- ')可以得到ID(1)的结果,怎么样才能把动态SQL插入静态的SQL语句中去
EXEC('.... WHERE ID IN (SELECT ID---) ')
@sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls)
--如何将exec执行结果放入变量中? declare @num int,
@sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
[/code]
SELECT ROW_NUMBER() OVER (ORDER BY tmp.AddTime DESC) AS RowNumber,ID,NO,Name,Weight,Price,Discount,Quantity,Video,Supplier,Description,IsCertificate,IsDisplay,AddTime
FROM
(SELECT DISTINCT ProductPart.ID,NO,Name,Weight,Price,Discount,ProductPart.Quantity,Video,Supplier, Description,IsCertificate,IsDisplay,AddTime
FROM ProductPart
INNER JOIN CatalogProductPart ON CatalogProductPart.PID=ProductPart.ID
LEFT OUTER JOIN ProductStruct ON ProductStruct.ProductID=ProductPart.ID
WHERE IsDisplay=1 AND CatalogProductPart.CatalogID IN (SELECT CatalogID FROM dbo.GetTree(0))
AND (ProductPart.ID IN(SELECT ID FROM #T) OR ProductStruct.PartID IN (SELECT ID FROM #T))
)tmp
消息 156,级别 15,状态 1,第 1 行
关键字 'SELECT' 附近有语法错误。
消息 102,级别 15,状态 1,第 1 行
')' 附近有语法错误。还是不成功??!!!
--one method:
declare @StrSQL nvarchar(4000)
set @StrSQL = 'SELECT ID FROM tablename'select id into #T from tablename where 1=2
insert #T exec(@StrSQL)INSERT INTO @ProductPart
SELECT ROW_NUMBER() OVER (ORDER BY tmp.AddTime DESC) AS RowNumber,ID,NO,Name,Weight,Price,Discount,Quantity,Video,Supplier,Description,IsCertificate,IsDisplay,AddTime
FROM
(SELECT DISTINCT ProductPart.ID,NO,Name,Weight,Price,Discount,ProductPart.Quantity,Video,Supplier, Description,IsCertificate,IsDisplay,AddTime
FROM ProductPart
INNER JOIN CatalogProductPart ON CatalogProductPart.PID=ProductPart.ID
LEFT OUTER JOIN ProductStruct ON ProductStruct.ProductID=ProductPart.ID
WHERE IsDisplay=1 AND CatalogProductPart.CatalogID IN (SELECT CatalogID FROM dbo.GetTree(0))
AND (ProductPart.ID IN(SELECT ID FROM #T) OR ProductStruct.PartID IN (SELECT ID FROM #T))
)tmp
exec(@strsql)
@strsql的内容是什么,怎么产生并不知道。
print出来看看。
===========================2,
insert @productPart select row_number()...
这一段是与exec(@strsql)在一个批中的语句,还是指 @strsql的内容是它?如果insert这句位于exec之后,即二句作为一个批运行那么至少可以看出的语法错误是:
insert 表 select row_number() ...
row_number()之类窗口函数不能直接用在其它操作中,它只能是产生结果集用。
可以用一个子查询来操作,比如
insert 表 select * from (select row_number() ..... ) x如果insert这句是指的@strsql的内容,那么至少有两个语法错误:
(1) row_number的使用,我上面说过了;
(2) 动态语句中向表变量插入值,表变量如果在外部定义,动态语句是认不到的。===================================3,exec(....)的结果集,不能直接串在查询语句中使用
你的 in(exec (....)) 是不被语法支持的。
一般可以采用两种方式实现:
(1)先建立临时表,然后 insert 临时表 exec ..
再 in (select ... from 临时表)
这种作法适合于exec返回的结果集结构可预知的情况
(2)使用openrowset之类语句
in (select ... from openrowset(......'exec....'))
这种作法适合于exec近回的结果集结构不可预知的情况下,因为结果集结构无法预知的话,不可能事先定义临时表结构。
select id from openrowset( 'SQLOLEDB', 'HAPPY'; 'SA'; '20071004','select id from db_test.dbo.tb')
怎么加到(ProductPart.ID IN(-----)去,没看懂你的意思
这一步是最关键的,后门应用#T表名就可以了,谢谢大家
INSERT INTO @ProductPart
SELECT ROW_NUMBER() OVER (ORDER BY tmp.AddTime DESC) AS RowNumber,ID,NO,Name,Weight,Price,Discount,Quantity,Video,Supplier,Description,IsCertificate,IsDisplay,AddTime
FROM
(SELECT DISTINCT ProductPart.ID,NO,Name,Weight,Price,Discount,ProductPart.Quantity,Video,Supplier, Description,IsCertificate,IsDisplay,AddTime
FROM ProductPart
INNER JOIN CatalogProductPart ON CatalogProductPart.PID=ProductPart.ID
LEFT OUTER JOIN ProductStruct ON ProductStruct.ProductID=ProductPart.ID
WHERE IsDisplay=1 AND CatalogProductPart.CatalogID IN (SELECT CatalogID FROM dbo.GetTree(0))
AND (ProductPart.ID IN(SELECT ID FROM #T) OR ProductStruct.PartID IN (SELECT ID FROM #T))
)tmp79 zssl001 钻石手链001 5.6 8600.00 1 8600 100 1 钻石手链001 0 1 2008-12-18 14:05:30.437
77 zses001 钻石耳饰001 1.3 5400.00 1 5400 100 1 钻石耳饰001 0 1 2008-12-18 13:51:09.420
75 zsdz001 钻石吊坠001 4.8 3700.00 0.4 1480 10 1 钻石吊坠001 1 1 2008-12-18 13:38:49.293
74 zsxl001 钻石项链001 9.8 8900.00 0.9 8010 5 1 钻石项链001 1 1 2008-12-18 13:33:21.390
72 zsjz005 钻石戒指005 5.5 3200.00 1 3200 8 1 钻石戒指005 1 1 2008-12-18 12:25:07.000
71 zsjz004 钻石戒指004 2.5 1800.00 0.59 1062 5 1 钻石戒指004 1 1 2008-12-18 12:20:14.980
70 zsjz003 钻石戒指003 4.8 7200.00 0.33 2376 10 1 钻石戒指003 1 1 2008-12-18 12:14:01.640
69 zsjz002 钻石戒指002 3.6 4800.00 0.24 1152 100 1 钻石戒指002 1 1 2008-12-18 12:09:48.340
68 zsjz001 钻石戒指001 2.5 3500.00 0.4 1400 100 1 钻石戒指001 1 1 2008-12-18 12:03:53.153
16 zsss004 钻石女戒2 7.5 2900.00 0.9 2610 994 Default.swf 1 钻石女戒2 1 1 2008-11-27 16:38:06.637
1 diamond001 钻石001 2.5 2000.00 1 2000 1 NULL 1 钻石001 1 1 2008-11-27 16:10:13.590
SELECT ROW_NUMBER() OVER (ORDER BY tmp.AddTime DESC) AS RowNumber,ID,NO,Name,Weight,Price,Discount,Quantity,Video,Supplier,Description,IsCertificate,IsDisplay,AddTime
FROM
(SELECT DISTINCT ProductPart.ID,NO,Name,Weight,Price,Discount,ProductPart.Quantity,Video,Supplier, Description,IsCertificate,IsDisplay,AddTime
FROM ProductPart
INNER JOIN CatalogProductPart ON CatalogProductPart.PID=ProductPart.ID
LEFT OUTER JOIN ProductStruct ON ProductStruct.ProductID=ProductPart.ID
WHERE IsDisplay=1 AND CatalogProductPart.CatalogID IN (SELECT CatalogID FROM dbo.GetTree(0))
AND (ProductPart.ID IN(select id from openrowset( 'SQLOLEDB', 'HAPPY'; 'SA'; '20071004','select id from db_test.dbo.tb')) OR
ProductStruct.PartID IN (select id from openrowset( 'SQLOLEDB', 'HAPPY'; 'SA'; '20071004','select id from db_test.dbo.tb')))
)tmp