这个sql应该怎么写

[月光下的土豆]

Create proc GetStockFundBankCount
	@bankName nvarchar(40),
	
	@stockBankCount float output
as
	declare @bCount float
	
	--Select @bCount =Sum(StockAmount) FROM MPT_StockFund
	Set @querySql='SELECT '+ @bCount +'=Sum(StockAmount) FROM MPT_StockFund'
	exec(@querySql)
	if @bCount is null
		Set @bCount=0
	Set @stockBankCount = @bCount
Go

用注示掉的部分就可以正常运行。
因为要拼接SQl就改上面的写的。但是报错

[AcHerat]

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    declare @bCount float
    
    --Select @bCount =Sum(StockAmount) FROM MPT_StockFund
    Set @querySql='SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
    exec(@querySql)
    if @bCount is null
        Set @bCount=0
    Set @stockBankCount = @bCount
Go

[快溜]

 Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    declare @bCount float,@querySql varchar(1000)
    
    --Select @bCount =Sum(StockAmount) FROM MPT_StockFund
    Set @querySql='SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
    exec(@querySql)
    if @bCount is null
        Set @bCount=0
    Set @stockBankCount = @bCount
Go

[月光下的土豆]

这个试过了。程序提示

System.Data.SqlClient.SqlException: 必须声明标量变量 "@bCount"。

[中国风]

用sp_executesql

[AcHerat]

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    
    
    --Select @bCount =Sum(StockAmount) FROM MPT_StockFund
    Set @querySql='declare @bCount float  SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
    exec(@querySql)
    if @bCount is null
        Set @bCount=0
    Set @stockBankCount = @bCount
Go

[中国风]

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    declare @bCount float,@querySql nvarchar(4000)
    
    --Select @bCount =Sum(StockAmount) FROM MPT_StockFund
    Set @querySql='SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
    exec sp_executesql @querySql,N'@bCount float output',@bCount output
    if @bCount is null
        Set @bCount=0
    Set @stockBankCount = @bCount
Go

[AcHerat]

[Quote=引用 3 楼 mpt_hi 的回复:]
这个试过了。程序提示

System.Data.SqlClient.SqlException: 必须声明标量变量 "@bCount"。
[/Quote]

估计得都写进那个SQL里。

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
	declare @querySql varchar(max)
    Set @querySql='declare @bCount float  SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
    set @querySql = @querySql + ' if @bCount is null 
        Set @bCount=0
        Set '+@stockBankCount+' = @bCount'
	exec(@querySql)
Go

[月光下的土豆]

[Quote=引用 5 楼 acherat 的回复:]

SQL code

Create proc GetStockFundBankCount
@bankName nvarchar(40),

@stockBankCount float output
as


--Select @bCount =Sum(StockAmount) FROM MPT_StockFund
Set @querySql……
[/Quote]
程序通过了。但是@bCount=0.

[中国风]

給個例子樓主參照用法
输入输出

--用sp_executesql
declare @i int,@con int,@s nvarchar(1000)
set @i=5
select @s='select @con=count(1) from sysobjects where ID>@i'
exec sp_executesql @s,N'@con int output,@i int',@con output ,@i 
select @con

--用exec
declare @i int,@s nvarchar(1000)
set @i=5
select @s='declare @con int select @con=count(1) from sysobjects where ID>'+rtrim(@i)+' select @con'
exec(@s)

[Linares]

Create proc GetStockFundBankCount
	@bankName nvarchar(40),
	@stockBankCount float output
as
exec sp_executesql
	N'SELECT @stockBankCount = isnull(Sum(StockAmount),0) FROM MPT_StockFund',
	N'@stockBankCount float output',
	@stockBankCount output
go

[starseeker7]

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    declare @bCount float
    
    Select @bCount =Sum(cast(StockAmount as float)) FROM MPT_StockFund
    Set @querySql='SELECT '+ cast(@bCountas varchar(20)) +'=Sum(StockAmount) FROM MPT_StockFund'
    exec(@querySql)
    if @bCount is null
        Set @bCount=0
    Set @stockBankCount = @bCount
Go

这样行吗？

[starseeker7]

阿，理解弄错还是得6楼的办法呢

[幸运的意外]

用动态SQL sp_executesql最好了：

Create proc GetStockFundBankCount
@bankName nvarchar(40),

@stockBankCount float output
as
declare @bCount float,@querySql nvarchar(4000)
Set @querySql='SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund'
exec sp_executesql @querySql,N'@bCount float output',@bCount output
if @bCount is null
Set @bCount=0
select @stockBankCount = @bCount
go

[AcHerat]

[Quote=引用 10 楼 linares 的回复:]
SQL code
Create proc GetStockFundBankCount
@bankName nvarchar(40),
@stockBankCount float output
as
exec sp_executesql
N'SELECT @stockBankCount = isnull(Sum(StockAmount),0) FROM MPT_S……
[/Quote]

+1

--为什么一定要用动态呢？
Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    @stockBankCount float output
as
    SELECT @stockBankCount = isnull(Sum(StockAmount),0) FROM MPT_StockFund
go

[月光下的土豆]

[Quote=引用 14 楼 acherat 的回复:]

引用 10 楼 linares 的回复:
SQL code
Create proc GetStockFundBankCount
@bankName nvarchar(40),
@stockBankCount float output
as
exec sp_executesql
N'SELECT @stockBankCount = isnull(Sum(StockAmount),0)……
[/Quote]
我的SQL是拼接的。我粘了一部分

[月光下的土豆]

Create proc GetStockFundBankCount
	@bankName nvarchar(40),
	@dept nvarchar(40),
	@y nvarchar(40),
	@m nvarchar(40),
	@stockBankCount float output
as
	declare @bCount float
	declare @Sql nvarchar(1000)
	declare @querySql nvarchar(2000)
	Set @Sql=''
	
	if LEN(@dept)=0 
		Set @Sql=@Sql+''
	else
		Set @Sql=@Sql+' and StockDept='''+@dept+''''
		
	if LEN(@y)=0
		Set @Sql=@Sql+''
	else
		Set @Sql=@Sql+' and Year(StockPayDate)='+@y
	
	if LEN(@m)=0
		Set @Sql=@Sql+''
	else
		Set @Sql=@Sql+' and Month(StockPayDate)='+@m
	
	Set @querySql='SELECT @bCount=Sum(StockAmount) FROM MPT_StockFund Where StockPayBank='''+@bankName+''' and StockIsCheck <>'''''+@Sql
	
	exec sp_executesql @querySql,N'@bCount float output',@bCount output
	--set @querySql = @querySql + ' if @bCount is null 
 --       Set @bCount=0
 --       Set '+@stockBankCount+' = @bCount'

	exec(@querySql)
	if @bCount is null
		Set @bCount=0
	Set @stockBankCount = @bCount
Go

[ljking0731]

Create proc GetStockFundBankCount
    @bankName nvarchar(40),
    
    @stockBankCount float output
as
    set @querySql=' declare @bCount float 
select @bCount=sum(StockAmount) from MPT_StockFund' --在这里后面串什么内容都可以
+'if @bCount is null set @bCount=0
select @bCount as bCount into ##temp'
    
exec(@querySql)  
select @stockBankCount=bCount from ##temp
    
Go