Returning Different results sets with same Stored Procedure

Question

Post reply

Returning Different results sets with same Stored Procedure

ramrajan

SSCommitted

Points: 1737
More actions
June 13, 2014 at 12:11 am

#287444

The below stored procedure returning 20 rows
exec sp_executesql N'exec dbo.usp_Env_Inventory_GetTankDashboardDetails @userid,@PAGENUM,@PAGESIZE,@SortOrder,@SortColoumnName',N'@USERID int,@PAGENUM int,@PAGESIZE int,@SortOrder nvarchar(3),@SortColoumnName nvarchar(4000),@TankName nvarchar(45),@CustomerLocation_Filter nvarchar(4000),@TanksInventory_Filter nvarchar(4000),@TanksIManage_Filter bit,@Status nvarchar(4000),@OrderStatus nvarchar(4000),@ActiveAlerts nvarchar(4000),@CurrentHierarchyLevelID int,@CurrentHierarchyLevelType int,@XML nvarchar(4000)',@USERID=58183,@PAGENUM=1,@PAGESIZE=20,@SortOrder=N'ASC',@SortColoumnName=NULL,@TankName=N'A K STEEL CORP MIDDLETOWN, OH #2 BH Elimin-Ox',@CustomerLocation_Filter=N'',@TanksInventory_Filter=NULL,@TanksIManage_Filter=0,@Status=N'',@OrderStatus=N'',@ActiveAlerts=N'',@CurrentHierarchyLevelID=0,@CurrentHierarchyLevelType=0,@XML=NULL
after removing unnecessary things the same is giving me 1 record so what is the difference between above query and below.
exec dbo.usp_Env_Inventory_GetTankDashboardDetails
@userid=58183,@PAGENUM=1,@PAGESIZE=20,@SortOrder=N'',@SortColoumnName='',
@TankName=N'A K STEEL CORP MIDDLETOWN, OH #2 BH Elimin-Ox',
@CustomerLocation_Filter=N'',
@TanksInventory_Filter='',
@TanksIManage_Filter=0,@Status=N'',
@OrderStatus=N'',
@ActiveAlerts=N'',
@CurrentHierarchyLevelID=0
,@CurrentHierarchyLevelType=0,
@XML=NULL

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

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

Can you post the procedure dbo.usp_Env_Inventory_GetTankDashboardDetail?

That would help in answering you question.

Thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

ramrajan SSCommitted Points: 1737 More actions · Answer 2

Here is my SP

CREATE PROCEDURE dbo.usp_Env_Inventory_GetTankDashboardDetails(@USERID int ,

@PAGENUM int ,

@PAGESIZE int ,

@SortOrder varchar(50) = '' ,

@SortColoumnName varchar(50) = '' ,

@CustomerLocation_Filter varchar(max) = '' ,

@TanksInventory_Filter varchar(100) = '' ,

@TanksIManage_Filter bit = 0 ,

@TankName varchar(max) = '' ,

@status varchar(50) = '' ,

@OrderStatus varchar(50) = '' ,

@ActiveAlerts varchar(50) = '' ,

@CurrentHierarchyLevelID int = 0 ,

@CurrentHierarchyLevelType int = 0 ,

@XML xml = NULL)

Stuart Davies SSCoach Points: 19505 More actions · Answer 3

Hi - I think you are missing something from that - there are no statements there that would return any rows.

Please post the whole of the stored procedure.

Thanks

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

You're just showing us parameter sets. We can't tell anything about what's happening inside the stored procedure based on a set of parameters. We need to see the actual SELECT... FROM... WHERE... parts of the query.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning