SQL 我不能在表函数中使用“DECLARE”吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1158321/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't I use "DECLARE" in table function?
提问by Rapunzo
Thanks for your message I solved my problem like this
谢谢你的消息我这样解决了我的问题
ALTER FUNCTION [VEZNE].[fnMakbuzIslemGetir]
(
@refNo as int
)
RETURNS @tablename TABLE (kontrol1 char(1),key0 numeric(18,0) ,tarih datetime ,hizkod char(12),hizad char(75),ytlhizfiyat decimal(18,2) ,hizmiktar numeric(18,2),ytlhiztutar decimal(18,2))
AS
BEGIN
DECLARE @durum AS VARCHAR
DECLARE @hastaTuru AS VARCHAR
DECLARE @makTipi AS VARCHAR
SET @durum = (SELECT durum FROM TH_RefKart WHERE RefNo = @refNo)
SET @hastaturu = (SELECT HastaTuru FROM TH_Dosya WHERE DosyaNo in (SELECT DosyaNo FROM TH_RefKart WHERE RefNo = @refNo))
SET @makTipi = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo )
IF ((@hastaTuru = 'ücretli') AND (@durum = 'AYAKTA')) OR @makTipi = '0'
BEGIN
INSERT @tablename SELECT kontrol1, key0, tarih, hizkod, hizad, ytlhizfiyat, hizmiktar, ytlhiztutar
FROM TH_Islem
WHERE refno= @refNo and DekontNo = '0' and anahtar<> -1
END
RETURN
END
but now I have another problem at this row
但现在我在这一行遇到了另一个问题
SET @makTipi = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo)
because this SELECT returns multi rows value. what can I do now?
因为这个 SELECT 返回多行值。我现在能做什么？
采纳答案by Brian
Ok, so (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) returns multiple values, what to do now depends on what you want to do. So if you want the maximum value from those results use:
好的，所以 (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) 返回多个值，现在做什么取决于你想做什么。因此，如果您想从这些结果中获得最大值，请使用：
(SELECT MAX(maktipi) FROM TH_Islem WHERE refNo = @refNo )
Or MIN:
或 MIN：
(SELECT MIN(maktipi) FROM TH_Islem WHERE refNo = @refNo )
Or as @makTipi is used later on to check for something, maybe you do want to retain all the values returned. In that case you'd need to change the @makTipi to a table and insert all the values returned from (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) into it. Such as:
或者稍后使用 @makTipi 来检查某些内容，也许您确实希望保留所有返回的值。在这种情况下，您需要将 @makTipi 更改为表并将从 (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo ) 返回的所有值插入其中。如：
DECLARE @makTipi TABLE(columName AS VARCHAR)
INSERT @makTipi SELECT maktipi FROM TH_Islem WHERE refNo = @refNo
Then later on where you have:
然后在你有：
IF ((@hastaTuru = 'ücretli') AND (@durum = 'AYAKTA')) OR @makTipi = '0'
Change this to:
将此更改为：
IF ((@hastaTuru = 'ücretli') AND (@durum = 'AYAKTA')) OR EXISTS (SELECT * FROM @makTipi WHERE columnName = '0' )
Hope this helps!
希望这可以帮助！
回答by Brian
Try this:
尝试这个：
CREATE FUNCTION [dbo].[fnMakbuzIslem]
(
@refNo as int
)
RETURNS @tablename TABLE (kontrol1 INT,key0 INT ,tarih INT ,hizkod INT,hizad INT,ytlhizfiyat INT,hizmiktar INT,ytlhiztutar INT)
AS
BEGIN
DECLARE @durum AS VARCHAR = (SELECT durum FROM TH_RefKart WHERE RefNo = @refNo)
DECLARE @hastaTuru AS VARCHAR = (SELECT HastaTuru FROM TH_Dosya WHERE DosyaNo in (SELECT DosyaNo FROM TH_RefKart WHERE RefNo = @refNo))
DECLARE @makTipi AS VARCHAR = (SELECT maktipi FROM TH_Islem WHERE refNo = @refNo)
IF ((@hastaTuru = 'ücretli') AND (@durum <> 'AYAKTA')) OR @makTipi = '0'
BEGIN
INSERT @tablename SELECT kontrol1,key0,tarih,hizkod,hizad,ytlhizfiyat,hizmiktar,ytlhiztutar
FROM TH_Islem
WHERE refno= @refNo and DekontNo = '0' and anahtar<> -1
END
RETURN
END
GO
WHERE all the columns are assumed to be INT's, change to suit your needs.
WHERE 所有列都假定为 INT，请根据您的需要进行更改。
