0

I have one stored procedure. The parameter in the stored procedure is dynamic.

I don't know to declare and use the parameter in code behind(VB.net) of my page.

This is the stored procedure:

CREATE PROCEDURE [dbo].[Gdata2]
    --@employeeID varchar(10), 
    --@employeeCostCenterCode varchar(20)
AS
BEGIN
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)

    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Product)
    FROM (SELECT DISTINCT Product FROM V_SBR_Product) AS Products

    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery =  N'SELECT Quarter, ' + @ColumnName + 
                                'FROM V_SBR_Product ' +
                                'PIVOT(COUNT(Total) FOR Product IN (' + @ColumnName + 
                                ')) AS PVTTable'

    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
END
T.S.
  • 18,195
  • 11
  • 58
  • 78
Munirah Malik
  • 3
  • 4
  • This is a stored procedured. and I create dynamic pivot table using this stored procedured. – Munirah Malik Dec 15 '14 at 09:02
  • The output from this stored procedure is: Quarter, Product1, Product2, Product3, Product4, ProductXX and so on. – Munirah Malik Dec 15 '14 at 09:03
  • VB.net doesn't care what is inside your sql procedure. Your parameters are commented out, so for vb.net they also mean nothing. Generally, you create your command- `Dim comm As new SqCommand("[dbo].[Gdata2]", CommandType.StoredProcedure)` And then add parameters like this - `comm.Parameters.AddWithValue`. This question is answered many times here on SO http://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue – T.S. Dec 15 '14 at 14:03

0 Answers0