declare @列名 varchar(888)set @列名='abc'select id from table1 where @列名 is not null是可以的。你都写出来了,为什么不在查询分析器里试试?
声明一下,以上是在sql server 2012中调试成功的。
哈哈 sql 2000,2005,2008应该是这样子的。 declare @列名 varchar(88) , @sql varchar(8000) set @列名='bh'set @sql='select * from employee where ' + @列名 + ' is not null'exec (@sql)
declare @列名 varchar(888)set @列名='abc'EXEC('select id from table1 where '+@列名+' is not null')
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb(id INT ,a INT ,b VARCHAR(20),c VARCHAR(10)) GO INSERT tb SELECT 1,1,NULL,'b' UNION ALL SELECT 2,2,'aa','x' UNION ALL SELECT 3,NULL,'a','b' GODECLARE @col VARCHAR(20) SET @col = 'b' SELECT c.* FROM (SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) a CROSS APPLY ( SELECT id = x1.value('./id[1]','INT') FROM x.nodes('//r') AS t(x1) WHERE x1.exist('./*[local-name(.)=sql:variable("@col")]') = 0 ) b CROSS APPLY tb c WHERE b.id = c.id/* 1 1 NULL b */
sql 2000,2005,2008应该是这样子的。
declare @列名 varchar(88) , @sql varchar(8000)
set @列名='bh'set @sql='select * from employee where ' + @列名 + ' is not null'exec (@sql)
谢谢你的提示,但是我弱弱的问下,如果写存储过程的话,可以用cookies或者session参数传入值吗?
我只听说过sql server 2008,还没听说2012呢,在哪里啊?
DROP TABLE tb
GO
CREATE TABLE tb(id INT ,a INT ,b VARCHAR(20),c VARCHAR(10))
GO
INSERT tb SELECT 1,1,NULL,'b'
UNION ALL SELECT 2,2,'aa','x'
UNION ALL SELECT 3,NULL,'a','b'
GODECLARE @col VARCHAR(20)
SET @col = 'b'
SELECT c.* FROM
(SELECT x=(SELECT * FROM tb FOR XML PATH('r'),TYPE)) a
CROSS APPLY
(
SELECT id = x1.value('./id[1]','INT')
FROM x.nodes('//r') AS t(x1)
WHERE x1.exist('./*[local-name(.)=sql:variable("@col")]') = 0
) b
CROSS APPLY tb c
WHERE b.id = c.id/*
1 1 NULL b
*/