create table test
(
ID INT PRIMARY KEY,
F1 INT,F2 INT,F3 INT,F4 INT,F5 INT,F6 INT,F7 INT,F8 INT,F9 INT,F10 INT
)
INSERT INTO TEST VALUES(1,5,5,9,6,5,6,3,4,2,6)
INSERT INTO TEST VALUES(2,3,5,6,6,7,1,5,8,7,6)
INSERT INTO TEST VALUES(3,4,5,2,6,5,6,8,6,9,6)
go
if OBJECT_ID('GETCOUNT','P') IS NOT NULL
DROP PROC GetCOUNT
GO
CREATE PROC GetCOUNT @Aid INT --@Aid 传输test的某一记录号
AS
DECLARE @F1 INT,@F2 INT,@F3 INT,@F4 INT,@F5 INT,@F6 INT,@F7 INT,@F8 INT,@F9 INT,@F10 INT
SELECT @F1=a.[F1],@F2=a.[F2],@F3=a.[F3],@F4=a.[F4],@F5=a.[F5],@F6=a.[F6],@F7=a.[F7],@F8=a.[F8],@F9=a.[F9],@F10=a.[F10]
FROM TEST a WHERE a.[ID]=@Aid 消息 102,级别 15,状态 1,过程 GetCOUNT,第 8 行
' ' 附近有语法错误。
(
ID INT PRIMARY KEY,
F1 INT,F2 INT,F3 INT,F4 INT,F5 INT,F6 INT,F7 INT,F8 INT,F9 INT,F10 INT
)
INSERT INTO TEST VALUES(1,5,5,9,6,5,6,3,4,2,6)
INSERT INTO TEST VALUES(2,3,5,6,6,7,1,5,8,7,6)
INSERT INTO TEST VALUES(3,4,5,2,6,5,6,8,6,9,6)
go
if OBJECT_ID('GETCOUNT','P') IS NOT NULL
DROP PROC GetCOUNT
GO
CREATE PROC GetCOUNT @Aid INT --@Aid 传输test的某一记录号
AS
DECLARE @F1 INT,@F2 INT,@F3 INT,@F4 INT,@F5 INT,@F6 INT,@F7 INT,@F8 INT,@F9 INT,@F10 INT
SELECT @F1=a.[F1],@F2=a.[F2],@F3=a.[F3],@F4=a.[F4],@F5=a.[F5],@F6=a.[F6],@F7=a.[F7],@F8=a.[F8],@F9=a.[F9],@F10=a.[F10]
FROM TEST a WHERE a.[ID]=@Aid 消息 102,级别 15,状态 1,过程 GetCOUNT,第 8 行
' ' 附近有语法错误。
create table test
(
ID INT
,F1 INT
,F2 INT
,F3 INT
,F4 INT
,F5 INT
,F6 INT
,F7 INT
,F8 INT
,F9 INT
,F10 INT
)INSERT INTO TEST VALUES(1,5,5,9,6,5,6,3,4,2,6)
INSERT INTO TEST VALUES(2,3,5,6,6,7,1,5,8,7,6)
INSERT INTO TEST VALUES(3,4,5,2,6,5,6,8,6,9,6)
goCREATE PROC GetCOUNT
@Aid INT --@Aid 传输test的某一记录号
AS
beginDECLARE @F1 INT,@F2 INT,@F3 INT,@F4 INT,@F5 INT,@F6 INT,@F7 INT,@F8 INT,@F9 INT,@F10 INTexec sp_executesql
N'SELECT @F1=a.[F1],@F2=a.[F2],@F3=a.[F3],@F4=a.[F4]
,@F5=a.[F5],@F6=a.[F6],@F7=a.[F7],@F8=a.[F8]
,@F9=a.[F9],@F10=a.[F10]
FROM your_test a WHERE a.[ID]=@Aid'
,N'@Aid int,@F1 int output,@F2 int output,@F3 int output
,@F4 int output,@F5 int output,@F6 int output,@F7 int output
,@F8 int output,@F9 int output,@F10 int output'
,@Aid = @Aid,@F1 = @F1 output,@F2 = @F2 output,@F3 = @F3 output,@F4 = @F4 output,@F5 = @F5 output
,@F6 = @F6 output,@F7 = @F7 output,@F8 = @F8 output,@F9 = @F9 output,@F10 = @F10 output;
print @F1;
print @F2;
print @F3;
print @F4;
print @F5;
print @F6;
print @F7;
print @F8;
print @F9;
print @F10;end--执行
exec GetCOUNT 1;--结果
/*
5
5
9
6
5
6
3
4
2
6
*/--总结:
--动态SQL中带参数需要用sp_executesql
后面可能有了全角的空格,删掉即可
--我傻了,根本就不需要动态sql
drop table test;create table test
(
ID INT
,F1 INT
,F2 INT
,F3 INT
,F4 INT
,F5 INT
,F6 INT
,F7 INT
,F8 INT
,F9 INT
,F10 INT
)INSERT INTO TEST VALUES(1,5,5,9,6,5,6,3,4,2,6)
INSERT INTO TEST VALUES(2,3,5,6,6,7,1,5,8,7,6)
INSERT INTO TEST VALUES(3,4,5,2,6,5,6,8,6,9,6)
goCREATE PROC GetCOUNT
@Aid INT --@Aid 传输test的某一记录号
AS
begin
DECLARE @F1 INT,@F2 INT,@F3 INT,@F4 INT,@F5 INT,@F6 INT,@F7 INT,@F8 INT,@F9 INT,@F10 INT
SELECT @F1=a.[F1],@F2=a.[F2],@F3=a.[F3],@F4=a.[F4]
,@F5=a.[F5],@F6=a.[F6],@F7=a.[F7],@F8=a.[F8]
,@F9=a.[F9],@F10=a.[F10]
FROM test a
WHERE a.[ID]=@Aid print @F1;
print @F2;
print @F3;
print @F4;
print @F5;
print @F6;
print @F7;
print @F8;
print @F9;
print @F10;
end--执行
exec GetCOUNT 1;
/*
--结果
5
5
9
6
5
6
3
4
2
6*/