如何使用动态sql的结果填充变量？ [英] How to fill a variable with result of a dynamic sql?

问题描述

大家好，



我制作了一个存储过程，我在其中创建了一个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!

推荐答案

无需转换为函数，您可以从存储过程中使用RETURN。

步骤如下：

Converting into a function is not necessary, you can use RETURN from a stored procedure.
The steps are:

1. 将SELECT语句的结果存储在临时表（#tmp），固定名称，一列，一条记录中（使用SELECT。 ..INTO ...）
2. 声明一个整数类型的@VARIABLE
3. SELECT @ VARIABLE = MAX（[FIELD]）FROM #tmp_TABLE
4. RETURN @ VARIABLE

希望这会有所帮助，

Pablo。

Hope this helps,
Pablo.

Skippy



我只是尝试将其转换为函数，所以现在你可以在sql查询中的任何地方使用它。

Hi Skippy

I just try to convert the same into a function, So now u can use it any where with in the sql query.

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的结果填充变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！