问题描述
大家好,
我制作了一个存储过程,我在其中创建了一个sql(基于2个给定的变量)。这个sql返回一个值(类型为int)。
现在我想通过调用这个过程在其他sqls中使用这个值,但我似乎没有得到任何回报,尽管动态sql总是有结果。
这是我的存储过程:
Hi all,
I made a stored procedure in which I create a sql (based on 2 given variables). This sql returns a single value (of type int).
Now I want to use this value in other sqls by calling this procedure, but I don''t seem to get any return, although the dynamic sql always has a result.
This is my stored procedure:
USE [DKOWare]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetNumberOfChildrenTrue]
@PKParentCriterium int, @PKPerson int, @return int output
AS
BEGIN
SET NOCOUNT ON;
declare @sql nvarchar(max)
declare @test nvarchar(max)
-- create the sql
-- this sql returns the column names of the fields to check
-- all those fields are bit values
-- I need the amount of fields set to TRUE
set @test = (select ''+ convert(int, isnull(['' + convert(nvarchar,pk) + ''],0)) '' from OTCriterium WITH (NOLOCK) WHERE ParentPK = @PKParentCriterium FOR XML PATH)
set @test = replace(@test, ''<row>'','''')
set @test = replace(@test, ''</row>'','''')
set @test = substring(@test,2,1000)
set @sql = ''select '' + @test + '' FROM PersonsCriteria WITH (NOLOCK) WHERE PKPersons = '' + @PKPerson
EXEC sp_executesql @sql
END
我假设我需要将程序转换为函数,但不要我不知道该怎么做。任何想法或解决方案?在此先感谢!
I assume I need to convert the procedure into a function, but don''t know how to do that. Any ideas or solutions? Thanks in advance!
推荐答案
- 将SELECT语句的结果存储在临时表(#tmp),固定名称,一列,一条记录中(使用SELECT。 ..INTO ...)
- 声明一个整数类型的@VARIABLE
- SELECT @ VARIABLE = MAX([FIELD])FROM #tmp_TABLE
- RETURN @ VARIABLE
希望这会有所帮助,
Pablo。
Hope this helps,
Pablo.
USE [DKOWare]
GO
CREATE FUNCTION [dbo].[GetNumberOfChildrenTrue] (@PKParentCriterium int, @PKPerson int)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
declare @sql nvarchar(max)
declare @test nvarchar(max)
declare @R_Int int
-- create the sql
-- this sql returns the column names of the fields to check
-- all those fields are bit values
-- I need the amount of fields set to TRUE
set @test = (select ''+ convert(int, isnull(['' + convert(nvarchar,pk) + ''],0)) '' from OTCriterium WITH (NOLOCK) WHERE ParentPK = @PKParentCriterium FOR XML PATH)
set @test = replace(@test, ''<row>'','''')
set @test = replace(@test, ''</row>'','''')
set @test = substring(@test,2,1000)
set @R_Int = ''select '' + @test + '' FROM PersonsCriteria WITH (NOLOCK) WHERE PKPersons = '' + @PKPerson
RETURN(@R_Int);
END;
SELECT [dbo].[GetNumberOfChildrenTrue](1,1) AS ''Child_Val'';
注意:我只是编写代码,您可能会遇到一些语法错误,所以要做好准备。
新年快乐...祝你好运:)
问候,
Vijay
NOTE : I just write code, you may be get some syntax error, So be ready for that.
Happy New Year ... Wish u good luck :)
Regards,
Vijay
这篇关于如何使用动态sql的结果填充变量?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!