大家好:
我写了一个存储过程,有一个问题,如下:
ALTER PROCEDURE [dbo].[P_ODS_initialize]
(
@ETL_DATE VARCHAR(10),
@DB_NAME VARCHAR(100),
@TB_NAME VARCHAR(200)
)
AS
BEGIN
定义省略........................
SELECT @pkeys=COLUMN_NAME FROM link_gz.[GZ_EOMP].INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='DictionaryLibrary' and CONSTRAINT_NAME like 'pk%';
..................................
我想把TABLE_NAME='DictionaryLibrary' 改成变量,不想写死,但怎么改都报错,有大神能帮忙改一下吗?
我写了一个存储过程,有一个问题,如下:
ALTER PROCEDURE [dbo].[P_ODS_initialize]
(
@ETL_DATE VARCHAR(10),
@DB_NAME VARCHAR(100),
@TB_NAME VARCHAR(200)
)
AS
BEGIN
定义省略........................
SELECT @pkeys=COLUMN_NAME FROM link_gz.[GZ_EOMP].INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='DictionaryLibrary' and CONSTRAINT_NAME like 'pk%';
..................................
我想把TABLE_NAME='DictionaryLibrary' 改成变量,不想写死,但怎么改都报错,有大神能帮忙改一下吗?
DECLARE @pkeys VARCHAR(100)=''
DECLARE @TB_NAME VARCHAR(200)='123'
SET @sql ='SELECT '+@pkeys+'=COLUMN_NAME
FROM link_gz.[GZ_EOMP].INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME='''+@TB_NAME+''''+' AND CONSTRAINT_NAME like '+'''pk%''';select @sql--EXEC(@sql) --运行的话执行这句lz参考
你好,那个select @sql是什么意思,我试了一下,@pkeys并没有数据
所有表都不是复合主键来的,我就想把主键的名字赋给@pkeys,但上面的语句执行的时候提示错误。
e.g.
ALTER PROCEDURE [dbo].[P_ODS_initialize]
(
@ETL_DATE VARCHAR(10),
@DB_NAME VARCHAR(100),
@TB_NAME VARCHAR(200)
)
AS
--BEGIN
DECLARE @pkeys VARCHAR(1000),@Sql VARCHAR(max)
SET @Sql='SET ?=STUFF((SELECT '',''+COLUMN_NAME FROM ['+@DB_NAME+'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='''+@TB_NAME+''' and CONSTRAINT_NAME like ''pk%'' FOR XML PATH('''')),1,1,'''')')
EXEC(@Sql,@pkeys OUTPUT) AT link_gz
SELECT @pkeys