27,579
社区成员
发帖
与我相关
我的任务
分享
DECLARE @s NVARCHAR(4000)
SET @target_cols = replace(''''+@target_cols+'''', ',', ''',''')
SET @s = ''
SELECT @s = @s + ',' + 'isnull('+QUOTENAME(name)+','''')'
FROM syscolumns
WHERE id = OBJECT_ID('[my_table]')
AND name IN ( @target_cols )
SET @s = STUFF(@s, 1, 1, '')
EXEC('select '+@s+' from [my_table]')
怎么来拆分额,这样得到的貌似是
name IN (’ 'col0','col1','col5','col7'‘ )
而不是
name IN ( 'col0','col1','col5','col7' )
--字符串拆分函数
CREATE FUNCTION [dbo].[fn_split]
(
@c NVARCHAR(MAX) ,
@split NVARCHAR(20) = ','
)
RETURNS @t TABLE ( col NVARCHAR(MAX) )
AS
BEGIN
WHILE ( CHARINDEX(@split, @c) <> 0 )
BEGIN
INSERT @t
( col
)
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) - 1)
)
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c) + LEN(@split) - 1,
'')
END
IF ( @c <> '' )
INSERT @t
( col )
VALUES ( @c )
RETURN
END
--调用函数
SELECT * FROM fn_split('col0,col1,col5,col7',',')
--结果
col
--------
1 col0
2 col1
3 col5
4 col7
创建字符串分割函数,然后根据3楼的动态SQL,将
AND name IN ( '希望显式的列' )
修改为
AND name IN (SELECT * FROM fn_split('col0,col1,col5,col7',','))
若有错误,先将拼成的动态SQL print出来再排查name IN ( ‘col0,col1,col5,col7’ )
实际需要为
name IN ( 'col0','col1','col5','col7' )
ALTER PROCEDURE [dbo].[mysp_query_record]
@id VarChar(32),
@target_cols VarChar(MAX)
AS
SET NOCOUNT ON
-- 如果@target_cols传参为"col0,col1,col5,col7"的话,是不是这样写就可以了?
EXEC('select '+@target_cols+' from [my_table] where [id] = ' + @id)
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[mysp_query_record]
@id VarChar(32),
@target_cols VarChar(MAX)
AS
SET NOCOUNT ON
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + 'isnull('+QUOTENAME(name)+','''')'
FROM syscolumns
WHERE id = OBJECT_ID('[my_table]')
AND name IN ( @target_cols ) -- 假设@target_cols传参为"col0,col1,col5,col7"
SET @s = STUFF(@s, 1, 1, '')
EXEC('select '+@s+' from [my_table] ') -- 查询的条件的话,是直接加到后面吗?比如:
-- EXEC('select '+@s+' from [my_table] where [id] = ' + @id)
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
问题有点多啊 麻烦了~~~ALTER PROCEDURE [dbo].[mysp_query_record]
@id VarChar(32),
@col0 VarChar(32)=null,
@col1 VarChar(32)=null,
@col2 VarChar(32)=null,
@col3 VarChar(32)=null,
@col4 VarChar(32)=null
AS
SET NOCOUNT ON
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT
@s = @s + ',' + 'isnull('+QUOTENAME(name)+','''')'
FROM
syscolumns
WHERE
[id] = OBJECT_ID('[my_table]')
AND name IN
(
(CASE WHEN isnull(@col0,'')='' THEN null ELSE '@col0' END),
(CASE WHEN isnull(@col1,'')='' THEN null ELSE '@col1' END),
(CASE WHEN isnull(@col2,'')='' THEN null ELSE '@col2' END),
(CASE WHEN isnull(@col3,'')='' THEN null ELSE '@col3' END),
(CASE WHEN isnull(@col4,'')='' THEN null ELSE '@col4' END)
)
SET @s = STUFF(@s, 1, 1, '')
EXEC('select '+@s+' from [my_table] where [id] = ' + @id)
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
GO
还不是很理解这些语句的意思,如果我要按照这个方法来实现我想要的功能的话
是不是按照我上面的改写方法就可以了呢?【偷懒减少了5列】
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + 'isnull('+QUOTENAME(name)+','''')'
FROM syscolumns
WHERE id = OBJECT_ID('表名')
AND name IN ( '希望显式的列' )
SET @s = STUFF(@s, 1, 1, '')
EXEC('select '+@s+' from 表名 ')