How to fill a variable with result of a dynamic sql?

Question

4.00/5 (1 vote)

See more:

SQL-Server-2005

, +

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 7:20am

Skippy II

Add a Solution

2 solutions

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 :)

Regards,
Vijay

Posted 26-Dec-12 20:05pm

P.S Vijay

Comments

Skippy II 27-Dec-12 15:08pm

Thanks for the suggestion!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pablo Aliskevicius · Accepted Answer · 2012-12-26T20:02:00

Solution 1

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

Store the results of your SELECT statement in a temporary table (#tmp), fixed name, one column, one record (using SELECT...INTO...)
Declare a @VARIABLE of type integer
SELECT @VARIABLE=MAX([FIELD]) FROM #tmp_TABLE
RETURN @VARIABLE

Hope this helps,
Pablo.

Posted 26-Dec-12 20:02pm

Pablo Aliskevicius

Comments

Skippy II 27-Dec-12 15:09pm

Got it working! Thanks a lot!