打破Sql Server功能 [英] Break in Sql Server Function

问题描述

大家好



请任何人都可以帮助我。

我想在我的函数中插入一个break关键字。

任何人都可以告诉我怎么做到这一点。



谢谢



< pre lang =SQL> ALTER FUNCTION [dbo]。[islistthere]
（
@ ent char （ 4 ）， @ Id int ， @ month char （ 4 ）， @ sec < span class =code-keyword> int
）
RETURNS char （ 5 ） AS
BEGIN
DECLARE @ Table1 int
DECLARE @ Table2 int
DECLARE @添加 char （ 5 ）
SET @ Add = ' false'

SET @ Table1 =（ SELECT COUNT（PRID） FROM PR < span class =code-keyword> WHERE PRID = @ Id 和 ent = ltrim （rtrim（ @ ent ））和 TMonth = @ month）
SET @ Table2 =（ SELECT COUNT（ PRDID） FROM PRD WHERE PRID = @ Id andent = ltrim（rtrim（ @ ent ））和 TMonth = @ month 和 TSec = @ sec）

IF （ @ sec = 0 AND @ Table1 = 0 ）
SET @ Add = ' true'


IF （ @ sec > 0 AND @ Table1 = 1 AND @ Table2 = 0 ）
SET @ Add = ' true'





RETURN @ Add
END

解决方案

你正在寻找的是如何调试SQL函数。

见这里：演练：调试T-SQL用户定义函数 [ ^ ]

OriginalGriff已经提到了一个很好的链接。



但我想你想在SSMS中检查你的SP部分。（不在你的代码）像exec spname



所以你可以使用return来达到这个目的。

 ALTER FUNCTION [dbo]。[islistthere] 
（
 @ent char（4），@ Id int，@ monmon char（4），@ sec int 
）
 RETURNS char（5）AS 
 BEGIN 
 DECLARE @ Table1 int 
 DECLARE @ Table2 int 
 DECLARE @Add char（5）
 SET @Add ='false'
 
 SET @ Table1 =（SELECT COUNT（PRID）FROM PR WHERE PRID = @Id and ent = ltrim（rtrim（@ent））and TMonth = @ month ）
 
 select @ Table1 
 return  - 它将从这里返回并且不会执行其他语句
 
 SET @ Table2 =（SELECT COUNT（PRDID）FROM PRD WHERE PRID = @Id andent = ltrim（rtrim（@ent））和TMonth = @ month和TSec = @ sec）
 
 IF（@sec = 0 AND @ Table1 = 0）
 SET @Add ='true'
 
 
 IF（@sec> ; 0和@Table1 = 1 AND @ Table2 = 0）
 SET @Add ='true'
 
 
 
 
 
 RETURN @Add 
结束

但是一旦你完成检查，不要忘记从sp中删除这些额外的返回，否则它会给出错误/错误的输出。

Hi All

Please any one can help me.
I want to insert a break keyword in my Function.
Can anyone let me know how I can achieve this.

Thanks

ALTER FUNCTION [dbo].[islistthere]
(
	@ent	char(4), 	@Id int, @month char(4), @sec int
)
RETURNS char(5)  AS
BEGIN
	DECLARE @Table1 int
	DECLARE @Table2 int
	DECLARE @Add char(5)
	SET @Add = 'false'

	SET @Table1 = ( SELECT COUNT(PRID) FROM PR WHERE PRID = @Id and ent = ltrim(rtrim(@ent)) and TMonth=@month)
	SET @Table2 = ( SELECT COUNT(PRDID) FROM PRD WHERE PRID = @Id andent = ltrim(rtrim(@ent)) and TMonth=@month and TSec=@sec)

	IF (@sec = 0 AND @Table1 = 0 )
		SET @Add = 'true'	

 
	IF (@sec > 0 AND @Table1 = 1 AND @Table2 = 0 )
		SET @Add = 'true'
	

	
	
		
	RETURN @Add 
END

解决方案

Probably, what you are looking for is how to debug an SQL function.
See here: Walkthrough: Debugging a T-SQL User-Defined Function[^]

OriginalGriff has mentioned a nice link.

but I think you want to check you SP in parts in SSMS.(Not in your code) like "exec spname"

so you can use return for this purpose.

ALTER FUNCTION [dbo].[islistthere]
(
	@ent	char(4), 	@Id int, @month char(4), @sec int
)
RETURNS char(5)  AS
BEGIN
	DECLARE @Table1 int
	DECLARE @Table2 int
	DECLARE @Add char(5)
	SET @Add = 'false'
 
	SET @Table1 = ( SELECT COUNT(PRID) FROM PR WHERE PRID = @Id and ent = ltrim(rtrim(@ent)) and TMonth=@month)

select @Table1
return -- it will return from here and would not execute other statements

	SET @Table2 = ( SELECT COUNT(PRDID) FROM PRD WHERE PRID = @Id andent = ltrim(rtrim(@ent)) and TMonth=@month and TSec=@sec)
 
	IF (@sec = 0 AND @Table1 = 0 )
		SET @Add = 'true'	
 
 
	IF (@sec > 0 AND @Table1 = 1 AND @Table2 = 0 )
		SET @Add = 'true'
	
 
	
	
		
	RETURN @Add 
END

but once you done with checking don't forget to remove these extra "return" from your sp, Otherwise it will give error/wrong output.

这篇关于打破Sql Server功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！