Select statements included within a function cannot return data to a client: SQL server 2014

Question

Select statements included within a function cannot return data to a client: SQL server 2014

dimpythewimpy

Ten Centuries

Points: 1286
More actions
September 19, 2016 at 9:34 am

#330301

I am writing a scalar valued function in SQL Server which takes input from 3 tables (uses a left outer join) and returns 1 variable called response_string. Here's the function code:
CREATE FUNCTION [hsip].[getTempResponseById](
@cRegion CHAR(2) = '00',
@cState_Code CHAR(2) = '00',
@nFY NUMERIC(4,0) = 0,
@nREPORT_ID NUMERIC(2,0) = 0,
@nSECTION_ID NUMERIC(2,0) = 0,
@nSUBSECTION_ID NUMERIC(2,0) = 0,
@nQUESTION_NUMBER NUMERIC(38,0) = 0,
@nDISPLAY_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
@sUSER_ID VARCHAR(25) = NULL,
@nROW_NUMBER AS INTEGER,
@nCOLUMN_NUMBER AS INTEGER)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @sRESPONSE_STRING VARCHAR
set @sRESPONSE_STRING =(SELECT QD.FY_ST_QUESTION_DTL_TABLE_ID,qi.display_number,QI.QUESTION_NUMBER,
qd.question_part_number
from FY_ST_QUESTION_INFO qi,
FY_ST_QUESTION_DETAIL qd
where qi.region = @cRegion
and qi.state_code = @cState_Code
and qi.fy = @nFY
and qi.report_id = @nREPORT_ID
and qi.section_id = @nSECTION_ID
and qi.subsection_id = @nSUBSECTION_ID
and qi.display_number = @nDISPLAY_NUMBER
and qi.QUESTION_NUMBER = @nQUESTION_NUMBER
and ( QI.REPORTER_ID = @sUSER_ID or
QI.DELEGATE_ID = @sUSER_ID or
QI.SUB_DELEGATE_ID = @sUSER_ID )
and QD.FY_ST_QUESTION_INFO_TABLE_ID = QI.FY_ST_QUESTION_INFO_TABLE_ID)fq
left outer join
( select distinct FY_ST_QUESTION_DTL_TABLE_ID, AD.RESPONSE_STRING
from UPLOAD_TEMP_DATA ad
where ad.region = @cRegion
and ad.state_code = @cState_Code
and ad.fy = @nFY
and ad.report_id = @nREPORT_ID
and ad.section_id = @nSECTION_ID
and ad.subsection_id = @nSUBSECTION_ID
and ad.display_number = @nDISPLAY_NUMBER
and ad.QUESTION_NUMBER = @nQUESTION_NUMBER
and ad.QUESTION_PART_NUMBER = @nQUESTION_PART_NUMBER
and AD.ROW_NUMBER = @nROW_NUMBER
and AD.COLUMN_NUMBER = @nCOLUMN_NUMBER
)aq
on fq.fy_st_question_dtl_table_id = aq.FY_ST_QUESTION_DTL_TABLE_ID
RETURN @sRESPONSE_STRING;
Can someone suggest a workaround for this. I have researched online and tried inline function but,that didn't work as I am not returning a table here. It is just a single variable.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

You can't assign the results of a query that returns multiple rows or columns to the value of a variable. This is not allowed:

DECLARE @sRESPONSE_STRING VARCHAR

set @sRESPONSE_STRING = (SELECT col1, col2, etc FROM someTable);

What single, atomic value are you attempting to assign to @sRESPONSE_STRING?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

dimpythewimpy Ten Centuries Points: 1286 More actions · Answer 2

@alan: The response string is coming from UPLOADTEMPDATA table from the code. Thanks

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 3

dimpythewimpy (9/19/2016)
Can someone suggest a workaround for this. I have researched online and tried inline function but,that didn't work as I am not returning a table here. It is just a single variable.

One can easily use an inline table valued function that returns one row, no problem there and much more efficient in majority of cases. Suggest you post the DDL (create table) for the tables involved, sample data as an insert statement, parameter values and expected results.

😎

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

The left-outer-joined derived table (the bottom half of the query) isn't referenced in the outer SELECT, WHERE clause or ORDER BY. You can safely remove it.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

dimpythewimpy Ten Centuries Points: 1286 More actions · Answer 5

I tried to convert the above function to inline table valued function, but was unable to do the same. Can some one provide an example of inline function using joins. Really appreciate the help!! Thanks

Eirikur Eiriksson SSC Guru Points: 182910 More actions · Answer 6

dimpythewimpy (9/19/2016)
I tried to convert the above function to inline table valued function, but was unable to do the same. Can some one provide an example of inline function using joins. Really appreciate the help!! Thanks

Suggest you post the DDL (create table) for the tables involved, sample data as an insert statement, parameter values and expected results.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

dimpythewimpy (9/19/2016)
I am writing a scalar valued function in SQL Server which takes input from 3 tables (uses a left outer join) and returns 1 variable called response_string. Here's the function code:
CREATE FUNCTION [hsip].[getTempResponseById](
@cRegion CHAR(2) = '00',
@cState_Code CHAR(2) = '00',
@nFY NUMERIC(4,0) = 0,
@nREPORT_ID NUMERIC(2,0) = 0,
@nSECTION_ID NUMERIC(2,0) = 0,
@nSUBSECTION_ID NUMERIC(2,0) = 0,
@nQUESTION_NUMBER NUMERIC(38,0) = 0,
@nDISPLAY_NUMBER NUMERIC(38,0) = 0,
@nQUESTION_PART_NUMBER NUMERIC(38,0) = 0,
@sUSER_ID VARCHAR(25) = NULL,
@nROW_NUMBER AS INTEGER,
@nCOLUMN_NUMBER AS INTEGER)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @sRESPONSE_STRING VARCHAR
set @sRESPONSE_STRING =(SELECT QD.FY_ST_QUESTION_DTL_TABLE_ID,qi.display_number,QI.QUESTION_NUMBER,
qd.question_part_number
from FY_ST_QUESTION_INFO qi,
FY_ST_QUESTION_DETAIL qd
where qi.region = @cRegion
and qi.state_code = @cState_Code
and qi.fy = @nFY
and qi.report_id = @nREPORT_ID
and qi.section_id = @nSECTION_ID
and qi.subsection_id = @nSUBSECTION_ID
and qi.display_number = @nDISPLAY_NUMBER
and qi.QUESTION_NUMBER = @nQUESTION_NUMBER
and ( QI.REPORTER_ID = @sUSER_ID or
QI.DELEGATE_ID = @sUSER_ID or
QI.SUB_DELEGATE_ID = @sUSER_ID )
and QD.FY_ST_QUESTION_INFO_TABLE_ID = QI.FY_ST_QUESTION_INFO_TABLE_ID)fq
left outer join
( select distinct FY_ST_QUESTION_DTL_TABLE_ID, AD.RESPONSE_STRING
from UPLOAD_TEMP_DATA ad
where ad.region = @cRegion
and ad.state_code = @cState_Code
and ad.fy = @nFY
and ad.report_id = @nREPORT_ID
and ad.section_id = @nSECTION_ID
and ad.subsection_id = @nSUBSECTION_ID
and ad.display_number = @nDISPLAY_NUMBER
and ad.QUESTION_NUMBER = @nQUESTION_NUMBER
and ad.QUESTION_PART_NUMBER = @nQUESTION_PART_NUMBER
and AD.ROW_NUMBER = @nROW_NUMBER
and AD.COLUMN_NUMBER = @nCOLUMN_NUMBER
)aq
on fq.fy_st_question_dtl_table_id = aq.FY_ST_QUESTION_DTL_TABLE_ID
RETURN @sRESPONSE_STRING;
Can someone suggest a workaround for this. I have researched online and tried inline function but,that didn't work as I am not returning a table here. It is just a single variable.
Thanks

Looking at this code, I don't see it working the way you expect.

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 8

dimpythewimpy (9/19/2016)
Can someone suggest a workaround for this. I have researched online and tried inline function but,that didn't work as I am not returning a table here. It is just a single variable.

Read the following article. It's not just about how to make scalar functions run faster. It's also about how to use an iTVF to return a Scalar Value. You'll have to change the way you use it compared to a true Scalar Function but paradigm shift is easy and makes for very fast code.

http://www.sqlservercentral.com/articles/T-SQL/91724/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)