CREATE PROCEDURE cp_ttt
@tableSponsor nvarchar(20)
AS
declare @tableNameCreate nvarchar(20);
declare @sqlTableCreate nvarchar(200) set @tableNameCreate = 'tableOne';
set @sqlTableCreate = N' Create @tableName
( orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10)
) '
execute sp_executesql @sqlTableCreate,
N'@tableName nvarchar(20)',
@tableNameCreate set @tableNameCreate = 'tableTwo'; execute sp_executesql @sqlTableCreate,
N'@tableName nvarchar(20)',
@tableNameCreate
GO
@tableSponsor nvarchar(20)
AS
declare @tableNameCreate nvarchar(20);
declare @sqlTableCreate nvarchar(200) set @tableNameCreate = 'tableOne';
set @sqlTableCreate = N' Create @tableName
( orgID nchar (10),
orgRegSubject nchar (10),
subjectTitle nvarchar (10),
subjectSummary nvarchar (10)
) '
execute sp_executesql @sqlTableCreate,
N'@tableName nvarchar(20)',
@tableNameCreate set @tableNameCreate = 'tableTwo'; execute sp_executesql @sqlTableCreate,
N'@tableName nvarchar(20)',
@tableNameCreate
GO
SQL codeCREATE PROCEDURE cp_ttt
@tableSponsor nvarchar(20)
AS 我只看出来这点差别,但是存储过程,有参数的话,是要加上小括号的
(
@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
N'exec()'这种还真没有见过,呵呵,exec的效率不是太高。
能够正确运行
总结了一下 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 虽然更在参数列表中,但是是没有什么意义的。是以前写的。现在才明白。呵呵