Click here to Skip to main content
12,078,370 members (47,864 online)
Rate this:
 
Please Sign up or sign in to vote.
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!
Posted 26-Dec-12 8:20am
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Converting into a function is not necessary, you can use RETURN from a stored procedure.
The steps are:
  1. Store the results of your SELECT statement in a temporary table (#tmp), fixed name, one column, one record (using SELECT...INTO...)
  2. Declare a @VARIABLE of type integer
  3. SELECT @VARIABLE=MAX([FIELD]) FROM #tmp_TABLE
  4. RETURN @VARIABLE

Hope this helps,
Pablo.
  Permalink  
Comments
Skippy II 27-Dec-12 15:09pm
   
Got it working! Thanks a lot!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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';

NOTE : I just write code, you may be get some syntax error, So be ready for that.

Happy New Year ... Wish u good luck Smile | :)

Regards,
Vijay
  Permalink  
Comments
Skippy II 27-Dec-12 15:08pm
   
Thanks for the suggestion!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160212.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100