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

Question

Post reply

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

dimpythewimpy

Ten Centuries

Points: 1286

September 19, 2016 at 9:51 am

#1901666

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

Eirikur Eiriksson SSC Guru Points: 182631 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

dimpythewimpy

Ten Centuries

Points: 1286

September 19, 2016 at 1:07 pm

#1901724

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: 182631 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: 442447 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: 1000539 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://qa.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)
Intro to Tally Tables and Functions