得不到…… Msg 156, Level 15, State 1, Line 2 在关键字 'SELECT' 附近有语法错误。
--DECLARE @A INT SELECT COUNT(*) FROM dbo.UserBank这样就行,第一句不要 create table UserBank(id int) insert into UserBank select 1 union all select 2 go create procedure getct as select count(*) from dbo.UserBank go exec getct /* ----------- 2(1 行受影响)*/ go drop table UserBank drop procedure getct
是的,有一个输出参数,是拿到总数。请问如何做?这样做不行啊?就算普通这样,我输入也出错DECLARE @A INT SET @A = SELECT COUNT(*) FROM dbo.UserBank PRINT @AMsg 156, Level 15, State 1, Line 2 在关键字 'SELECT' 附近有语法错误。
DECLARE @A INT SET @A = (SELECT COUNT(*) FROM dbo.UserBank)
或者DECLARE @A INT SELECT @A =COUNT(*) FROM dbo.UserBank PRINT @A
DECLARE @A INT SET @A = EXEC('SELECT COUNT(*) FROM UserBank') PRINT @A这样不行哦?因为有时我的SQL语句是拼凑起来的
这样就行,select的语句也能赋值
但是“WHERE BANKID=100” 这句以变量传入,如何执行呢?
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' SELECT @A=COUNT(*) FROM BANK PRINT @A条件全部放在一个变量,怎么办?
SELECT @A =COUNT(*) FROM dbo.UserBank
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B exec(@B)
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B exec(@B)Msg 137, Level 15, State 1, Line 1 必须声明变量 '@A'。
DECLARE @A INT SELECT @A = COUNT(*) FROM dbo.UserBank SELECT @A
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B exec(@B)Msg 137, Level 15, State 1, Line 1 必须声明变量 '@A'。我需要不同条件的总数
由于总数需要在不同条件下获取,所以做成了以下代码DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B exec(@B)Msg 137, Level 15, State 1, Line 1 必须声明变量 '@A'。 但就是出错
DECLARE @A INT DECLARE @sql nVARCHAR(100) SET @sql='WHERE BANKID<>0' set @sql=N'SELECT @A =COUNT(*) FROM dbo.UserBank '+@sqlexec sp_executesql @sql,N'@A dec(10) output',@A output;print @A
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT '+@A+' =COUNT(*) FROM dbo.UserBank'+@B exec(@B) --条件都知道写在''外面了,变量不知道吗?...
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' Exec('SELECT '+@A+' =COUNT(*) FROM dbo.UserBank'+@B) --直接执行就可以了,赋值给@B估计又出错了. --条件都知道写在''外面了,变量不知道吗?...
DECLARE @A INT SELECT @A=COUNT(*) FROM dbo.UserBank select @A
DECLARE @A INT DECLARE @B VARCHAR(100) SET @B=' WHERE BANKID<>0' set @B=N'SELECT COUNT(*) FROM dbo.UserBank'+@B exec(@B) set @A=@@rowcount print (@A)
DECLARE @A INT SELECT @A = COUNT(*) FROM dbo.UserBank select @A
DECLARE @A INT SELECT @A = COUNT(*) FROM dbo.UserBank select @A
由于总数需要在不同条件下获取,所以做成了以下代码DECLARE @A INT DECLARE @B VARCHAR(100) SET @B='WHER BANKID<>0' set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B exec(@B)Msg 137, Level 15, State 1, Line 1 必须声明变量 '@A'。 但就是出错
if OBJECT_ID('CONTACT','u') is not null drop table CONTACT goCREATE TABLE CONTACT( TEAM nchar(5) NULL, ENGLISH_NAME nvarchar(30) NULL, CHINESE_NAME nvarchar(30) NULL ) GOinsert into CONTACT select 'ms','name1','cname1' union all select 'ms','name2','cname2' union all select 'ms','name3','cname3' union all select 'ms','name4','cname4' union all select 'ms','name5','cname5' union all select 'ms','name6','cname6' union all select 'ms','name7','cname7' union all select 'ms','name8','cname8' union all select 'ms','name9','cname8' goif OBJECT_ID('p_getCntByTeam','p') is not null drop procedure p_getCntByTeam go create procedure p_getCntByTeam( @teamName varchar(100), @cnt int out ) as begin select @cnt = COUNT(*) from CONTACT where TEAM = @teamName return end go declare @cnt intexec p_getCntByTeam @teamName = 'ms', @cnt = @cnt out select @cnt go 代码直接贴进SQL SERVER MANAGERMENT 里面测试
declare @i as int select @i =count(*) from dbo.UserBank
DECLARE @A INT --赋值 SELECT @A = COUNT(*) FROM dbo.UserBank --显示输出 SELECT @A
DECLARE @A INT SET @A = (SELECT COUNT(*) FROM appdata..inpatf3 where pat_id = 181562) SELECT @A
DECLARE @A INT SELECT @A = COUNT(*) FROM dbo.UserBank select @A
Msg 156, Level 15, State 1, Line 2
在关键字 'SELECT' 附近有语法错误。
SELECT COUNT(*) FROM dbo.UserBank这样就行,第一句不要
create table UserBank(id int)
insert into UserBank select 1 union all select 2
go
create procedure getct
as
select count(*) from dbo.UserBank
go
exec getct
/*
-----------
2(1 行受影响)*/
go
drop table UserBank
drop procedure getct
是的,有一个输出参数,是拿到总数。请问如何做?这样做不行啊?就算普通这样,我输入也出错DECLARE @A INT
SET @A = SELECT COUNT(*) FROM dbo.UserBank
PRINT @AMsg 156, Level 15, State 1, Line 2
在关键字 'SELECT' 附近有语法错误。
SET @A = (SELECT COUNT(*) FROM dbo.UserBank)
SELECT @A =COUNT(*) FROM dbo.UserBank
PRINT @A
SET @A = EXEC('SELECT COUNT(*) FROM UserBank')
PRINT @A这样不行哦?因为有时我的SQL语句是拼凑起来的
这样就行,select的语句也能赋值
但是“WHERE BANKID=100”
这句以变量传入,如何执行呢?
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
SELECT @A=COUNT(*) FROM BANK
PRINT @A条件全部放在一个变量,怎么办?
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)Msg 137, Level 15, State 1, Line 1
必须声明变量 '@A'。
DECLARE @A INT
SELECT @A = COUNT(*) FROM dbo.UserBank
SELECT @A
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)Msg 137, Level 15, State 1, Line 1
必须声明变量 '@A'。我需要不同条件的总数
由于总数需要在不同条件下获取,所以做成了以下代码DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)Msg 137, Level 15, State 1, Line 1
必须声明变量 '@A'。
但就是出错
DECLARE @sql nVARCHAR(100)
SET @sql='WHERE BANKID<>0'
set @sql=N'SELECT @A =COUNT(*) FROM dbo.UserBank '+@sqlexec sp_executesql @sql,N'@A dec(10) output',@A output;print @A
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT '+@A+' =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)
--条件都知道写在''外面了,变量不知道吗?...
DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
Exec('SELECT '+@A+' =COUNT(*) FROM dbo.UserBank'+@B)
--直接执行就可以了,赋值给@B估计又出错了.
--条件都知道写在''外面了,变量不知道吗?...
DECLARE @A INT
SELECT @A=COUNT(*) FROM dbo.UserBank
select @A
DECLARE @B VARCHAR(100)
SET @B=' WHERE BANKID<>0'
set @B=N'SELECT COUNT(*) FROM dbo.UserBank'+@B
exec(@B)
set @A=@@rowcount
print (@A)
SELECT @A = COUNT(*) FROM dbo.UserBank
select @A
SELECT @A = COUNT(*) FROM dbo.UserBank
select @A
由于总数需要在不同条件下获取,所以做成了以下代码DECLARE @A INT
DECLARE @B VARCHAR(100)
SET @B='WHER BANKID<>0'
set @B=N'SELECT @A =COUNT(*) FROM dbo.UserBank'+@B
exec(@B)Msg 137, Level 15, State 1, Line 1
必须声明变量 '@A'。
但就是出错
输出参数 select @a=count(*) from tb; 在程序上就是那个参数运行后的值
以集合形式 select @a; 用 executeScalar 取出
还要以返回值形式 return @a ; -- 这里严重不推荐,因为存储过程本来有个返回值 表示是否执行成功。 用returnvalue取出
这里重新再数了一次数量,如果要求的不是count这句就用不了了
if OBJECT_ID('CONTACT','u') is not null
drop table CONTACT
goCREATE TABLE CONTACT(
TEAM nchar(5) NULL,
ENGLISH_NAME nvarchar(30) NULL,
CHINESE_NAME nvarchar(30) NULL
)
GOinsert into CONTACT
select 'ms','name1','cname1'
union all
select 'ms','name2','cname2'
union all
select 'ms','name3','cname3'
union all
select 'ms','name4','cname4'
union all
select 'ms','name5','cname5'
union all
select 'ms','name6','cname6'
union all
select 'ms','name7','cname7'
union all
select 'ms','name8','cname8'
union all
select 'ms','name9','cname8'
goif OBJECT_ID('p_getCntByTeam','p') is not null
drop procedure p_getCntByTeam
go
create procedure p_getCntByTeam(
@teamName varchar(100),
@cnt int out
)
as
begin
select @cnt = COUNT(*) from CONTACT where TEAM = @teamName
return
end
go
declare @cnt intexec p_getCntByTeam
@teamName = 'ms',
@cnt = @cnt out
select @cnt
go 代码直接贴进SQL SERVER MANAGERMENT 里面测试
select @i =count(*) from dbo.UserBank
--赋值
SELECT @A = COUNT(*) FROM dbo.UserBank
--显示输出
SELECT @A
SET @A = (SELECT COUNT(*) FROM appdata..inpatf3 where pat_id = 181562)
SELECT @A
SELECT @A = COUNT(*) FROM dbo.UserBank
select @A