Question

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

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

Answer 1

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

Answer 2

以下是我做过的有临时表参与的存储过程,参考下吧,应该不难的,具体不明再问
---创建存储过程

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

Answer 3

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

Answer 4

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