这个sql应该怎么写

2011-02-23 02:27:21 177 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

妙啊，受益终生的20个写SQL的小细节，这次终于学到了前言 SQL 语句的优化对于研发人员也是非常重要的，高效的 SQL语句经常会给使一个业务逻辑的接口响应速度变得非常快。每一个好习惯都是一笔财富，本文分SQL后悔药，SQL性能优化，SQL规范优雅三个方向，写SQL语句一些小细节的分享！另外本人整理了20年面试题大全，包含spring、并发、数据库、Redis、分布式、dubbo、JVM、微服务等方面总结，下图是部分截图，需要的话点这里点这里，暗号CSDN。 1. 写完SQL先explain查看执行计划（SQL性能优化）日常开发写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就改上面的写的。但是报错

...全文

AI已经进化到SQL随便写的地步了吗？ AI已经进化到随便写SQL了吗？AI已经进化到给表结构，给业务报表，给业务逻辑，sql语句轻松生成的地步了吗？阅读详情

月光下的土豆

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

ljking0731 2011-02-23

打赏
举报

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

月光下的土豆 2011-02-23

打赏
举报



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

月光下的土豆 2011-02-23

打赏
举报

[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是拼接的。我粘了一部分

AcHerat 元老 2011-02-23

打赏
举报

[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

幸运的意外 2011-02-23

打赏
举报

用动态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

starseeker7 2011-02-23

打赏
举报

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

starseeker7 2011-02-23

打赏
举报



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

这样行吗？

Linares 2011-02-23

打赏
举报

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

中国风 2011-02-23

打赏
举报

給個例子樓主參照用法

输入输出



--用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)

月光下的土豆 2011-02-23

打赏
举报

[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.

AcHerat 元老 2011-02-23

打赏
举报

[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

中国风 2011-02-23

打赏
举报

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 元老 2011-02-23

打赏
举报



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

中国风 2011-02-23

打赏
举报

用sp_executesql

月光下的土豆 2011-02-23

打赏
举报

这个试过了。程序提示

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

快溜 2011-02-23

打赏
举报

 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

AcHerat 元老 2011-02-23

打赏
举报



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应该怎么写

相关推荐