Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Stored Procedure explanation Expand / Collapse
Author
Message
Posted Sunday, January 27, 2013 11:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:11 AM
Points: 78, Visits: 233
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,
Post #1412196
Posted Monday, January 28, 2013 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
What bit don't you understand?

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

Mack
Post #1412294
Posted Monday, January 28, 2013 7:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:57 PM
Points: 13,058, Visits: 11,884
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1412436
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse