alter PROCEDURE ptest
(
@n int
)
as
begin
declare @s1 nvarchar(4000), @s2 nvarchar(4000), @s3 nvarchar(4000)
select @s1='select id from sysobjects ', @s2=' where id<@n '
print ' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n'
EXEC(' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n')
endexec ptest 10
------------------------------
必须声明变量 '@n'。
我的 @n是从外面传递进入的,怎么传入.详细请看http://topic.csdn.net/u/20080107/18/376e74aa-19d0-49d1-a520-aeca2da96de5.html
(
@n int
)
as
begin
declare @s1 nvarchar(4000), @s2 nvarchar(4000), @s3 nvarchar(4000)
select @s1='select id from sysobjects ', @s2=' where id<@n '
print ' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n'
EXEC(' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n')
endexec ptest 10
------------------------------
必须声明变量 '@n'。
我的 @n是从外面传递进入的,怎么传入.详细请看http://topic.csdn.net/u/20080107/18/376e74aa-19d0-49d1-a520-aeca2da96de5.html
set sql =N'select id from' +tablename + 'where id' <@n
(
@n int
)
as
begin
declare @s1 nvarchar(4000), @s2 nvarchar(4000), @s3 nvarchar(4000)
select @s1=' select id from sysobjects ', @s2=' where id <@n '
select @s3=' declare @n int set @n='+rtrim(@n)
print @s3+' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n'
EXEC(@s3+' Exec sp_executeSql N'''+@s1+@s2+ N''', N''@n int'', @n')
end
goexec ptest 20
/*
declare @n int set @n=20 Exec sp_executeSql N' select id from sysobjects where id <@n ', N'@n int', @n
id
-----------
4
5
7
8
13
15(6 row(s) affected)
*/drop procedure ptest