January 27, 2013 at 11:28 pm
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,
January 28, 2013 at 3:30 am
What bit don't you understand?
If the answer is all of it then you may need to look at TSQL tutorials online...
Mack
January 28, 2013 at 7:43 am
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