Click here to Skip to main content
Rate this: bad
good
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 at 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 at 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
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Maciej Los 250
3 Shweta N Mishra 216
4 PIEBALDconsult 174
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,665


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 27 Dec 2012
Copyright © CodeProject, 1999-2014
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