Stored procedure if pass two parameters i got the result but if i pass one parameter i did not get any result but in the result message box giving the result like "Command(s) completed successfully.", Kindly help to resolve this issue.

Question

Stored procedure if pass two parameters i got the result but if i pass one parameter i did not get any result but in the result message box giving the result like "Command(s) completed successfully.", Kindly help to resolve this issue.

DBA.k

Ten Centuries

Points: 1105
More actions
December 29, 2014 at 9:06 pm

#302062

ALTER PROCEDURE [dbo].[SP_NiceUserresultsearch_test]
-- Add the parameters for the stored procedure here
@iFormID Varchar(4),
@AgentUserID Varchar(8) = NULL
--@CreationTimefrom varchar(10) = NULL,
--@CreationTimeto varchar(10) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @columns AS NVARCHAR(MAX);
DECLARE @Pivot AS NVARCHAR(MAX);
select @columns = STUFF((SELECT ',' +
QUOTENAME(ncvReportLabel)
FROM (select distinct iQuestionID,ncvReportLabel,SubHierarchy from dbo.VIEW_NICEEvaluations_New where iFormID = '' +@iFormID+ '' ) sub order by SubHierarchy,iQuestionID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '') ;
SELECT @Pivot =
'select * from (
select
iFormID,
iEvalID,
iEvaluatedObjectID,
AgentUserID,
ncvReportLabel,
nvcQuestionAnswer,
EvalUserID,
dtCreationTime,
CallStartTime,
/*CallEndTime,
iEvaluatedDuration,
dtModificationTime,
dateadd("hh",5.5,dtEvaluatedStartTime) as dtEvaluatedStartTime ,
BusinessUnit,
Division,
Department,
Section,
Team,
FirstName,
Surname,
PositionTitle,
LeaderID,
LeaderName,
LeaderPosition,
LORID,
LORName,*/
flMaxScore,
flscore / flMaxScore as [Score%],
Batchdate
from
dbo.VIEW_NICEEvaluations_New where iFormID = coalesce( ''' +@iFormID+ ''' , iFormID ) and AgentUserID = coalesce( ''' +@AgentUserID+ ''' , AgentUserID )
) TMP
PIVOT
(
MAX([nvcQuestionAnswer])
FOR [ncvReportLabel] IN (' + @columns + ' )' + '
) as pvt ;';
exec (@Pivot);
--END
END
when i execute the below procedure I got perfect result:
exec [SP_NiceUserresultsearch_test] '725','U339858'
when I execuete below procedure I got the result like " Command(s) completed successfully. "
exec [SP_NiceUserresultsearch_test] '725'

Viewing 4 posts - 1 through 3 (of 3 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Quick thought, passing NULL to the @AgentUserID parameter will set the dynamic pivot statement concatenation to NULL, consider this example

😎

DECLARE @PARAM_NULL VARCHAR(8) = NULL;

/* NULL concatenation */

SELECT 'A STRING CONCAT TO NULL VALUE PARAM (' + @PARAM_NULL + ') WILL FAIL!';

/* ISNULL concatenation */

SELECT 'A STRING CONCAT TO NULL VALUE PARAM (' + ISNULL(@PARAM_NULL,'') + ') WILL NOT FAIL IF NULL IS SUBSTITUDED!';

DBA.k Ten Centuries Points: 1105 More actions · Answer 2

Thanks for yours reply,

I tried ISNULL parameter as per concatenation but still I am getting 0 Result set, means only execute and given that columns headers

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

mahi123 (12/30/2014)
Thanks for yours reply,
I tried ISNULL parameter as per concatenation but still I am getting 0 Result set, means only execute and given that columns headers

That's because of the filtering on the AgentUserID column, my guess is that there is no empty string value in that column. You need to change the dynamic code so the filter is removed if no value is passed.

😎