Hi,

Check the following:

https://startrule.wordpress.com/2014/08/16/smartobjects-as-data-sources-for-drop-downs/

Here's how I have accomplished it

1. Create a stored procedure. In my case, I wanted only distinct values

USE  TempDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE  dbo]..SP_Get_Departments]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DISTINCT 
       Dept_BU_Clean]
FROM  TempLaborDB]..dbo]..KS13]

END

GO

2.  Create a SmartObject for your stored procedure

Click Finish, and then run

@LT_Jason, 

That is a great explaination, I was working on something similar to that today. 

Do you have a way to add a parameter from the stored proc into a smartObject?

Say you added 

    @number int

and you had 

WHERE this = @number

Do you know there to add the @number parameter in the smartObject?

USE bTempDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  PROCEDURE Tdbo].OSP_Get_Departments]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DISTINCT 
      TDept_BU_Clean]
FROM aTempLaborDB].[dbo].aKS13]

END

GO

Hi Chadg,

The same parameter you add in the stored procedure will appear as smartobject parameter in the smartobject

If you don't see parameters in your smartobject based on SQL stored procedure, you need to check if you are allowing parameters to be used in smartobjects.

to check that go to the SQL service instance and make sure that the value of "Use parameters for stored procedures" = True

wish this helps

thanks guys. I was sorted