Call Stored Procedure or Sql Query in Cognos Report Studio 10

Stored Procedure in SQL Server.

USE [Gosales]

GO

/****** Object:  StoredProcedure [dbo].[spProductlineDetailstest]    Script Date: 07/18/2014 22:03:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Vikram

-- Create date: 01-11-2013

-- Description:   Using one query item to show mulitiple parameters.

-- =============================================

Create PROCEDURE [dbo].[spProductlineDetailstest]

      -- Add the parameters for the stored procedure here

                  @PRODUCTLINECD NVARCHAR(10)

                     

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 PRODUCT_LINE_CODE,PRODUCT_LINE_EN FROM Gosales.PRODUCT_LINE

               WHERE Gosales.PRODUCT_LINE.PRODUCT_LINE_CODE = @PRODUCTLINECD

END

--EXEC [dbo].[spProductlineDetailstest] '991'

In Cognos report studio:

Add SQL Query in Query Explorer, then add the below query in SQL Query and Validate the query.

{EXEC [Gosales].[dbo].[spProductlineDetailstest] #Prompt('ProductLineCD','Integer')#}

Sql Query Call in Cognos Report Studio:

{Select * from gosales.Product_Line where Product_Line_Code = #Prompt('ProductLineCD','Integer')#}