22,209
社区成员
发帖
与我相关
我的任务
分享
ALTER PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1 '
IF(@classno IS NOT NULL)
SET @sqlcommand = concat(@sqlcommand,' and classno = @classno')
IF(@studentno IS NOT NULL)
SET @sqlcommand = concat(@sqlcommand,' and studentno in( @studentno)')
SET @parm = '@classno varchar(20),@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno,@studentno = @studentno
END
CREATE PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',
@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1'
IF(@classno IS NOT NULL)
SET @sqlcommand = @sqlcommand+' and classno = '''+ @classno+''')'
IF(@studentno IS NOT NULL)
SET @sqlcommand = @sqlcommand+' and studentno in ('+@studentno+')'
SET @parm = '@classno varchar(20),@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno,@studentno = @studentno
END
ALTER PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1 '
IF(@classno IS NOT NULL)
SET @sqlcommand = concat(@sqlcommand,' and classno = @classno')
IF(@studentno IS NOT NULL)
--改动的代码
SET @studentno=REPLACE(@studentno,',',''',''')
SET @sqlcommand = concat(@sqlcommand,' and studentno in( '+@studentno+')')
SET @parm = '@classno varchar(20)'--,@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno--,@studentno = @studentno
END
实在不行这样改下看,把参数先传进去[/quote]
Procedure execution failed
42000 - [SQL Server]“,”附近有语法错误。 反复修改了几次,还是报错。[/quote]
alter PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
--DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
--set @sqlcommand = 'select * from guest.stscore where 1=1 '
--IF(@classno IS NOT NULL)
--SET @sqlcommand = concat(@sqlcommand,' and classno = @classno')
--IF(@studentno IS NOT NULL)
--SET @sqlcommand = concat(@sqlcommand,' and studentno in( @studentno)')
--SET @parm = '@classno varchar(20),@studentno varchar(20)'
--EXEC sp_executesql @sqlcommand,@parm,@classno = @classno,@studentno = @studentno
--以上你原来的代码都注释掉,运行下面的代码,看看能出来结果不
DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1 and studentno in( '+@studentno+')'
SET @studentno=REPLACE(@studentno,',',''',''')
SET @parm = '@classno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno
END
EXEC p_stscore 'class1','1001,1002'
CREATE PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',
@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1'
IF(@classno IS NOT NULL)
SET @sqlcommand = @sqlcommand+' and classno = '+ @classno+')'
IF(@studentno IS NOT NULL)
SET @sqlcommand = @sqlcommand+' and studentno in ('+@studentno+')'
SET @parm = '@classno varchar(20),@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno,@studentno = @studentno
END
ALTER PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1 '
IF(@classno IS NOT NULL)
SET @sqlcommand = concat(@sqlcommand,' and classno = @classno')
IF(@studentno IS NOT NULL)
--改动的代码
SET @studentno=REPLACE(@studentno,',',''',''')
SET @sqlcommand = concat(@sqlcommand,' and studentno in( '+@studentno+')')
SET @parm = '@classno varchar(20)'--,@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno--,@studentno = @studentno
END
实在不行这样改下看,把参数先传进去[/quote]
Procedure execution failed
42000 - [SQL Server]“,”附近有语法错误。 反复修改了几次,还是报错。
ALTER PROCEDURE [dbo].p_stscore
@classno varchar(20),@studentno varchar(20)
AS
BEGIN
DECLARE @parm nvarchar(max) =N'',@sqlcommand nvarchar(max) = N''
set @sqlcommand = 'select * from guest.stscore where 1=1 '
IF(@classno IS NOT NULL)
SET @sqlcommand = concat(@sqlcommand,' and classno = @classno')
IF(@studentno IS NOT NULL)
--改动的代码
SET @studentno=REPLACE(@studentno,',',''',''')
SET @sqlcommand = concat(@sqlcommand,' and studentno in( '+@studentno+')')
SET @parm = '@classno varchar(20)'--,@studentno varchar(20)'
EXEC sp_executesql @sqlcommand,@parm,@classno = @classno--,@studentno = @studentno
END
实在不行这样改下看,把参数先传进去
and charindex(studentno,@studentno)>0
EXEC sp_executesql @sqlcommand,
@parm,
@classno = @classno,
@studentno = REPLACE(@studentno,',',''',''')
改成这样再试试看[/quote]
这样写,保存时候直接报错了,报错代码如下:[/quote]
SET @studentno=REPLACE(@studentno,',',''',''')
EXEC sp_executesql @sqlcommand,
@parm,
@classno = @classno,
@studentno = @studentno
那就写在上面[/quote]
这样写,也还是出不来结果。我指的是多个studentno。 如果是单个值都是可以的。DROP TABLE [guest].[STSCORE]
GO
CREATE TABLE [guest].[STSCORE] (
[CLASSNO] nvarchar(MAX) NULL ,
[STUDENTNO] float(53) NULL ,
[NAME] nvarchar(MAX) NULL ,
[SEX] nvarchar(MAX) NULL ,
[COURSE] nvarchar(MAX) NULL ,
[GRADE] float(53) NULL
)
GO
-- ----------------------------
-- Records of STSCORE
-- ----------------------------
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1001', N'Jennifer', N'F', N'English', N'98')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1002', N'Tommy', N'M', N'English', N'89')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1003', N'Alex', N'M', N'English', N'91')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1004', N'Jonny', N'F', N'English', N'99')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1005', N'Tedesco', N'M', N'English', N'88')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1006', N'Scofield', N'M', N'English', N'87')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1007', N'Sara', N'F', N'English', N'90')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1008', N'Hiwwit', N'M', N'English', N'91')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1009', N'Jonnason', N'F', N'English', N'92')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1010', N'Achilles', N'M', N'English', N'93')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1011', N'Shark', N'M', N'English', N'94')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1012', N'Billy', N'M', N'English', N'89')
[/quote]
你这个表的STUDENTNO为什么要设置成float类型呢[/quote]
哦。不好意思。我自己从sqlite数据库传到sql server里面,自动改的。不是我有意的。DROP TABLE [guest].[STSCORE]
GO
CREATE TABLE [guest].[STSCORE] (
[CLASSNO] nvarchar(MAX) NULL ,
[STUDENTNO] float(53) NULL ,
[NAME] nvarchar(MAX) NULL ,
[SEX] nvarchar(MAX) NULL ,
[COURSE] nvarchar(MAX) NULL ,
[GRADE] float(53) NULL
)
GO
-- ----------------------------
-- Records of STSCORE
-- ----------------------------
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1001', N'Jennifer', N'F', N'English', N'98')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1002', N'Tommy', N'M', N'English', N'89')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1003', N'Alex', N'M', N'English', N'91')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1004', N'Jonny', N'F', N'English', N'99')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1005', N'Tedesco', N'M', N'English', N'88')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1006', N'Scofield', N'M', N'English', N'87')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1007', N'Sara', N'F', N'English', N'90')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1008', N'Hiwwit', N'M', N'English', N'91')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1009', N'Jonnason', N'F', N'English', N'92')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1010', N'Achilles', N'M', N'English', N'93')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1011', N'Shark', N'M', N'English', N'94')
GO
GO
INSERT INTO [guest].[STSCORE] ([CLASSNO], [STUDENTNO], [NAME], [SEX], [COURSE], [GRADE]) VALUES (N'Class1', N'1012', N'Billy', N'M', N'English', N'89')
[/quote]
你这个表的STUDENTNO为什么要设置成float类型呢
EXEC sp_executesql @sqlcommand,
@parm,
@classno = @classno,
@studentno = REPLACE(@studentno,',',''',''')
改成这样再试试看[/quote]
这样写,保存时候直接报错了,报错代码如下:[/quote]
SET @studentno=REPLACE(@studentno,',',''',''')
EXEC sp_executesql @sqlcommand,
@parm,
@classno = @classno,
@studentno = @studentno
那就写在上面
EXEC sp_executesql @sqlcommand,
@parm,
@classno = @classno,
@studentno = REPLACE(@studentno,',',''',''')