Solved

Using Stored Procedure for List View - Creating duplicate properties

  • 7 November 2017
  • 4 replies
  • 0 views

Although I've used stored procedures for execute commands against the database, this is the first time I'm using one for a list method.  

 

Basically, I want it to query the data slightly based on whether or not they're searching for completed records (<> 1) or incomple records (=1).  

 

When I try to create the Smart Object, however, it gives me two sets of properties UnitID, GroupID, UnitID(1), GroupID(1).  That doesn't work...

 

Anyway, my SPROC is as follows.  Anyone have any thoughts?

 

Rob

 

 

 

 

ALTER PROCEDURE [dbo].[Group_aggregated_sproc_2]

@Searchtype int

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here


if @searchtype = 1 begin

select UnitID, GroupID from recon_work_items
where (currentstatus = 1 or currentstatus > 20)
group by UnitID, GroupID

end

 

else

if @searchtype <> 1
begin
select UnitID, GroupID from recon_work_items
where currentstatus between 2 and 20
group by UnitID, GroupID


END
end

icon

Best answer by VJay 7 November 2017, 06:22

View original

4 replies

Hi Robert,

 

As there are 2 select statement in the stored procedure you are seeing two columns for each single column of table while creating a SMO.

 

Modify the Stored procedure to make dynamic query which means prepare the Query Statatement based on the conditions and execute the Query Statement at the end.

 

example:

 

Procedure:

Declare @Query NVARCHAR(Max)

If @searchtype = 1

Begin

   Set @Query = 'select UnitID, GroupID from recon_work_items where (currentstatus = 1 or currentstatus > 20) group by UnitID, GroupID'

End

else

if @searchtype <> 1
Begin
  Set @Query = select UnitID, GroupID from recon_work_items where currentstatus between 2 and 20 group by UnitID, GroupID

End

EXECUTE sp_executesql @Query

 

Check this link for the dynamic query

https://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

 

Regards,

Vijay.

Ahhhh.  Makes perfect sense!  I will try this!

 

Rob

 

 

Yeah!!

 

Let me know if it works!!!

 

VJay

It did work!

 

Rob

 

 

Reply