September 29, 2016 at 8:04 am
Hello Everyone,
I have a problem
This is the result what i get from my function ufn_GetHcmDesignFields
ISNULL(cast( ExtTotalExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtRelevantExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtSSNNumber as nvarchar) + ',' , '') from HC_RESUME_BANK where RID = 31418
So, I wrote the following query to get the result (This will be used in a procedure)
Declare @query nvarchar(max)
set @query = dbo.ufn_GetHcmDesignFields(3, 31418)
--Exec @query
Declare @query1 nvarchar(max)
set @query1 = 'select' + @query
EXEC sp_executesql @query1
The problem is I want the final result from " EXEC sp_executesql @query1" to be set to a variable so that can use it in select. like
select Variable as Alias
Thanks in Advance,
Sanjay
September 29, 2016 at 8:10 am
You can pass parameters to and from sp_executesql. See: https://msdn.microsoft.com/en-us/library/ms188001.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2016 at 3:41 am
Thanks Gail, Iam trying the following query and i get the error mentioned below.
Declare @query nvarchar(max)
set @query = dbo.ufn_GetHcmDesignFields(3, 31418)
Declare @query1 nvarchar(max)
set @query1 = N'Exec sp_executesql @query'
Declare @query2 nvarchar(max)
EXEC sp_executesql '@query1','@query2 nvarchar(max) output',@query2 output
select @query2 as Test
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
As mentioned above My function returns a "Select query" I am trying to execute that using sp_executesql
Pls suggest.
Thanks in advance
Sanjay
September 30, 2016 at 3:54 am
Your string literals need to be NVarchar, not Varchar, so N'string contents' rather than just 'string contents'
Also, @query1 shouldn't be in quotes, you want the executesql to execute the contents of that variable, not to try and execute the literal '@query1', which will just give you a syntax error.
set @query1 = N'Exec sp_executesql @query'
I have no clue why you're trying to do this, nested sp_executesql?? @Query1 should be the query you're trying to run, the SELECT @OutputVariable = ' + <function result>
All you needed to do was change the sp_executesql line in your original code and add the output variable into the SELECT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2016 at 6:54 am
I modified the query to
Declare @query nvarchar(max)
set @query = dbo.ufn_GetHcmDesignFields(3, 31418)
Declare @query2 nvarchar(max)
EXEC sp_executesql N'@query','@query2 nvarchar(max) output',@query2 output
select @query2 as Test
I get the same error
September 30, 2016 at 7:07 am
Your string literal need to be NVarchar, not Varchar.
@query shouldn't be in quotes, you want the executesql to execute the contents of that variable, not to try and execute the literal '@query', which will just give you a syntax error. You had it right in the original code.
The third parameter for sp_execute_sql is also incorrectly formed, go back and check the examples in the page I linked.
The value for @Query is just 'ISNULL(cast( ExtTotalExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtRelevantExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtSSNNumber as nvarchar) + ',' , '') from HC_RESUME_BANK where RID = 31418', which I'm sure you can see will throw a syntax error when run, as there's no SELECT and in addition you need to add @Query2 into that dynamic SQL to get the value assigned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2016 at 8:01 am
sanjaydut26 (9/30/2016)
I modified the query toDeclare @query nvarchar(max)
set @query = dbo.ufn_GetHcmDesignFields(3, 31418)
Declare @query2 nvarchar(max)
EXEC sp_executesql N'@query','@query2 nvarchar(max) output',@query2 output
select @query2 as Test
I get the same error
Can you post the code for function dbo.ufn_GetHcmDesignFields, please? This process could easily be a maintenance nightmare. Let's get that out of the way.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 2, 2016 at 11:26 pm
Here is the function...
ALTER FUNCTION [dbo].[ufn_GetHcmDesignFields]
(
@ModuleId bigint, -- HCM_DESIGN_FIELDS moduleid with IsExtendable status 1
@RID bigInt -- table rid
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Return as nvarchar(max), @ExtFields nvarchar(max)
set @Return=''
Set @ExtFields = (STUFF((SELECT '+' + ' ISNULL(cast( ' + FieldName + ' as nvarchar) + '','' , '''') '
FROM HCM_DESIGN_FIELDS with(NOLOCK) where IsExtendable = 1 and ModuleID = @ModuleId FOR XML PATH ('')), 1, 1, ''))
Set @Return = ('select' + @ExtFields + 'from HC_RESUME_BANK where RID = ' + cast(@RID as nvarchar(max)) )
RETURN @Return
END
October 4, 2016 at 8:34 am
sanjaydut26 (9/29/2016)
Hello Everyone,I have a problem
This is the result what i get from my function ufn_GetHcmDesignFields
ISNULL(cast( ExtTotalExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtRelevantExperienceUS as nvarchar) + ',' , '') + ISNULL(cast( ExtSSNNumber as nvarchar) + ',' , '') from HC_RESUME_BANK where RID = 31418
So, I wrote the following query to get the result (This will be used in a procedure)
Declare @query nvarchar(max)
set @query = dbo.ufn_GetHcmDesignFields(3, 31418)
--Exec @query
Declare @query1 nvarchar(max)
set @query1 = 'select' + @query
EXEC sp_executesql @query1
The problem is I want the final result from " EXEC sp_executesql @query1" to be set to a variable so that can use it in select. like
select Variable as Alias
Thanks in Advance,
Sanjay
You said that you are planning to return the result as "SELECT @YourVariable AS SomeAlias" right? And from what I see in your dynamic query you are only returning a single field. An easy way to acomplish what you want is with a temp table to store the result, since temp tables exists thru the whole process you could do the following:
CREATE TABLE #tmp(
Field NVARCHAR(MAX)
);
DECLARE @Query NVARCHAR(MAX);
SET @Query = dbo.ufn_GetHcmDesignFields(3, 31418);
--Exec @Query
SET @Query = N'
INSERT INTO #tmp(
Field
)
SELECT ' + @Query;
EXEC sp_executesql @Query;
SELECT
Field AS SomeAlias
FROM
#tmp
;
A. Mauricio Repetto
ML Engineer
October 4, 2016 at 8:44 am
sanjaydut26 (10/2/2016)
Here is the function...ALTER FUNCTION [dbo].[ufn_GetHcmDesignFields]
(
@ModuleId bigint, -- HCM_DESIGN_FIELDS moduleid with IsExtendable status 1
@RID bigInt -- table rid
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Return as nvarchar(max), @ExtFields nvarchar(max)
set @Return=''
Set @ExtFields = (STUFF((SELECT '+' + ' ISNULL(cast( ' + FieldName + ' as nvarchar) + '','' , '''') '
FROM HCM_DESIGN_FIELDS with(NOLOCK) where IsExtendable = 1 and ModuleID = @ModuleId FOR XML PATH ('')), 1, 1, ''))
Set @Return = ('select' + @ExtFields + 'from HC_RESUME_BANK where RID = ' + cast(@RID as nvarchar(max)) )
RETURN @Return
END
What's the reason for jumping through hoops to construct what is essentially a very simple query? Where's the benefit?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2016 at 3:13 pm
sanjaydut26 (10/2/2016)
Here is the function...ALTER FUNCTION [dbo].[ufn_GetHcmDesignFields]
(
@ModuleId bigint, -- HCM_DESIGN_FIELDS moduleid with IsExtendable status 1
@RID bigInt -- table rid
)
RETURNS nvarchar(max)
AS
BEGIN
DECLARE @Return as nvarchar(max), @ExtFields nvarchar(max)
set @Return=''
Set @ExtFields = (STUFF((SELECT '+' + ' ISNULL(cast( ' + FieldName + ' as nvarchar) + '','' , '''') '
FROM HCM_DESIGN_FIELDS with(NOLOCK) where IsExtendable = 1 and ModuleID = @ModuleId FOR XML PATH ('')), 1, 1, ''))
Set @Return = ('select' + @ExtFields + 'from HC_RESUME_BANK where RID = ' + cast(@RID as nvarchar(max)) )
RETURN @Return
END
It almost looks to me that you dont know what the fields are in the table HCM_DESIGN_FIELDS. Or maybe you are trying to account for the situation where the fields get added or replaced. I would be very careful here. If a underlying table gets changed, you may want it to error out so that you are aware of the change. Otherwise you will have a procedure that reports different fields as time goes by nullifying any documentation and expectations of it.
It really looks no different to me than a Select *.
----------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply