Stored Procedure explanation

  • Hi all,

    this is the first time am working on stored procedures, I need to make changes to the existing SP and to use new altered SP in my reports.

    Can anybody explain the follwing SP, then I may able to make changes to that and use.

    How can I go with this SP's while creating or modifying.

    please help me

    Create PROCEDURE [dbo].[getNotificateProduct]

    @search as varchar(1000),

    @store as varchar(50),

    @lan as varchar(2)

    AS

    BEGIN

    Declare @haku varchar(50)

    declare @temp as table (ParameterCaption varchar(100), ParameterValue varchar(100))

    IF substring(@search, LEN(@search),1)<>';'

    SET @search = @search + ';'

    while CHARINDEX(';',@search)>0

    BEGIN

    SET @haku=UPPER(LTRIM(cast(substring(@search,0, charindex(';',@search)) as varchar(50))))

    INSERT INTO @temp

    SELECT CASE

    WHEN @lan ='FI' THEN

    [Material_FI]

    WHEN @lan ='EN' THEN

    [Material_EN]

    END AS ParameterCaption,

    '[Material DIM].[Material FI].&['+ CAst([DimCubeProduct].[ProductKey] as varchar(50))+ ']' AS ParameterValue

    FROM [DimCubeProduct] INNER JOIN

    FactStoreQualityNotification ON [DimCubeProduct].[ProductKey] = FactStoreQualityNotification.[ProductKey] AND

    Charindex(Cast(FactStoreQualityNotification.StoreKey as varchar(5)),@store)>0

    WHERE (@haku = '') OR

    (

    (@lan='FI' AND CHARINDEX(@haku, UPPER([Material_FI])) > 0)

    OR

    (@lan='EN' AND CHARINDEX(@haku, UPPER([Material_EN])) > 0)

    )

    ORDER BY [ProductID]

    SET @search = substring(@search, charindex(';',@search)+1, LEN(@search) - 0)

    end

    SELECT DISTINCT * FROM @temp

    END

    GO

    Thanks,

  • What bit don't you understand?

    If the answer is all of it then you may need to look at TSQL tutorials online...

    Mack

  • Please don't cross post. Direct all replies here. http://www.sqlservercentral.com/Forums/Topic1412195-391-1.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 3 posts - 1 through 2 (of 2 total)

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