alter PROCEDURE cp_ttt
(
@tableSponsor nvarchar(20)
)
AS declare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'Create table '+@tableSponsor+'
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))' execute @sqlTableCreate
(
@tableSponsor nvarchar(20)
)
AS declare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'Create table '+@tableSponsor+'
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))' execute @sqlTableCreate
有 Table也不行
alter PROCEDURE cp_ttt
(
@tableSponsor nvarchar(20)
)
AS
begin
declare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'Create table '+@tableSponsor+'
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))' execute @sqlTableCreate
end
@tableName 没定义.CREATE PROCEDURE cp_ttt @tableSponsor nvarchar(20)
AS
begin
declare @sqlTableCreate nvarchar(200)
declare @tablename as varchar(10)
set @tablename = '...' -- or set @tablename = @tableSponsor
set @sqlTableCreate = N'Create table ' + @tableName +
'(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10)
)'
exec(@sqlTableCreate)
end
go
(
@tableSponsor nvarchar(20)
)
ASdeclare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'Create table '+@tableSponsor+N'
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))'
execute sp_executesql @sqlTableCreateGO
----
EXEC cp_ttt N'QQQ'
DROP TABLE QQQ
我想要用
execute sp_executesql @sqlTableCreate,
N'@tableName nvarchar(20)',
@tableSponsor
这个方法来实现
不都是sp_executesql 吗
你写的sp_executesql方法,参数有问题
(
@tableSponsor nvarchar(20)
)
ASdeclare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'Create table '+@tableSponsor+N'
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))'
execute sp_executesql @sqlTableCreateGO ----测试存储过程
EXEC cp_ttt N'QQQ'
DROP TABLE QQQ
cREATE PROCEDURE cp_ttt
(
@tableSponsor nvarchar(20)
)
ASdeclare @sqlTableCreate nvarchar(1000)
set @sqlTableCreate = N'exec(''Create table ''+@tableSponsor+''
(orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10))'')'
print @sqltablecreate
execute sp_executesql @sqlTableCreate,N'@tableSponsor varchar(20)',@tableSponsorGO
exec cp_ttt 'asf'
select * from asf
drop proc cp_ttt
drop table asf
能够正确运行
总结了一下 sp_executesql 与 参数 的关系 在sp_executesql 执行的字符串中, 下面称为spStr,有些是在执行前就必须指定的,有些是以变量的形式指定的。跟sql语句相一致,这里语句称为 sqlStr,凡是在sqlStr语句中必须要声明为常量的,在 执行 sp_executesql 前spStr 中的相应的部分,也必须已经被赋值了。如 create table constTableName ...中的constTableName , select top constNum...中的 constNum。 此贴就结了啊,呵呵
declare @nNum int;
set @nNum = @nPageSize * ( @nCurPage - 1 ); set @charSQL = N'select top ' + cast(@nNum as nvarchar(20)) + N' @nFirstID = nSceID ' +
N' from t_sceCardCN
where ( t_sceCardCN.nSceID between @nItemIDMin and @nItemIDMax )
and ( t_sceCardCN.nSceClass = @nSceClass and t_sceCardCN.nSceGrade =
@nSceGrade ) ' EXECUTE sp_executesql @charSQL
,N'@nNum int, @nFirstID int output , @nItemIDMin int, @nItemIDMax int, @nSceClass smallint, @nSceGrade smallint '
,@nNum, @nFirstID output , @nItemIDMin,@nItemIDMax, @nSceClass, @nSceGrade 说明:@nNum,在 sp_executesql 虽然更在参数列表中,但是是没有什么意义的。是以前写的。现在才明白。呵呵