SQL存储过程可以定义表变量吗？

答案:4 悬赏:50 手机版

解决时间 2021-02-12 09:34

提问者网友：送舟行
2021-02-11 19:16

比如把表做为参数，以下为错误方法，如果可以的话正确的如何写？ALTER PROCEDURE dbo.StoredProcedure1
@1 tableAS
select * from @1

最佳答案

五星知识达人网友：蓝房子
2021-02-11 20:12

表怎么定参数,可以用临时表 #tempTable
定义变量也得加个declare

全部回答

1楼网友：胯下狙击手
2021-02-11 23:14

这样不行吧。可以这样间接部分实现这种功能： ALTER PROCEDURE dbo.StoredProcedure1 @tpye int AS if @tpye =1 select * from table1 else if @tpye =2 select * from table1 ……

2楼网友：爱难随人意
2021-02-11 21:43

以下是我做过的有临时表参与的存储过程,参考下吧,应该不难的,具体不明再问 ---创建存储过程 USE BANK GO ---SELECt * FROM DEALING CREATE PROCEDURE PROC_DEALING AS ---建立DEALING_BUY临时表 DECLARE @DEALING_BUY TABLE ( ACCOUNT_CODE VARCHAr(32), FUND_CODE VARCHAr(32), STATUS VARCHAr(1), AMOUNT INT, TOTAL FLOAT ) INSERT INTO @DEALING_BUY(ACCOUNT_CODE,FUND_CODE,STATUS,AMOUNT,TOTAL) SELECt DEALING.ACCOUNT_CODE,DEALING.FUND_CODE,DEALING.STATUS,SUM(DEALING.AMOUNT),SUM(AMOUNT*VALUE) FROM DEALING WHERe STATUS='B' GROUP BY DEALING.ACCOUNT_CODE,DEALING.FUND_CODE,DEALING.STATUS ----SELECt * FROM @DEALING_BUY ---建立DEALING_BUY临时表 DECLARE @DEALING_SELL TABLE ( ACCOUNT_CODE VARCHAr(32), FUND_CODE VARCHAr(32), STATUS VARCHAr(1), AMOUNT INT, TOTAL FLOAT ) INSERT INTO @DEALING_SELL(ACCOUNT_CODE,FUND_CODE,STATUS,AMOUNT,TOTAL) SELECt DEALING.ACCOUNT_CODE,DEALING.FUND_CODE,DEALING.STATUS,SUM(DEALING.AMOUNT),SUM(AMOUNT*VALUE) FROM DEALING WHERe STATUS='S' GROUP BY DEALING.ACCOUNT_CODE,DEALING.FUND_CODE,DEALING.STATUS ---SELECt * FROM @DEALING_SELL SELECt T1.ACCOUNT_CODE,T1.FUND_CODE,IsNull(T1.AMOUNT-T2.AMOUNT,T1.AMOUNT) AS AMOUNT,T1.TOTAL/T1.AMOUNT AS VALUE ---FROM @DEALING_BUY T1,@DEALING_SELL T2 FROM @DEALING_BUY T1 LEFT JOIN @DEALING_SELL T2 ON T1.FUND_CODE=T2.FUND_CODE GO

3楼网友：長槍戰八方
2021-02-11 20:37

定义变量的方法： declare　@参数名　类型

我要举报

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息