Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Stored procedure explanation


Stored procedure explanation

Author
Message
niha.736
niha.736
SSC Journeyman
SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)SSC Journeyman (78 reputation)

Group: General Forum Members
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)
      Wink
      ORDER BY [ProductID]
      
      SET @search = substring(@search, charindex(';',@search)+1, LEN(@search) - 0)
   end
   
   SELECT DISTINCT * FROM @temp
END



GO



Thanks,
Niha
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39875
It would appear that a table variable is being populated based on a While Loop. The While Loop is based on the comma separated values that come in the @Search variable. The @Haku variable contains the "element" of the @Search string that is currently being worked on.

Personally, I'd split the @Search string using a decent non-looping splitter and do the whole stored rocedure as a single, set-based query (single query does not always constitute set-based).

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
niha.736 (1/27/2013)
How can I go with this SP's while creating or modifying.



if exists (select 1 from sysobjects where name = 'getNotificateProduct')
DROP PROCEDURE [dbo].[getNotificateProduct]
GO
Create PROCEDURE [dbo].[getNotificateProduct]
(
)
begin
....
....
end



IS this what your are asking ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
Bhuvnesh (1/28/2013)
niha.736 (1/27/2013)
How can I go with this SP's while creating or modifying.



if exists (select 1 from sysobjects where name = 'getNotificateProduct')
DROP PROCEDURE [dbo].[getNotificateProduct]
GO
Create PROCEDURE [dbo].[getNotificateProduct]
(
)
begin
....
....
end



IS this what your are asking ?



ALTER PROCEDURE [dbo].[getNotificateProduct]
...

will replace the text of the existing procedure with the next text you specify.

DROP will also drop all permissions and other things related to the proc (extended properties, etc.). That may not be a good idea in your environment.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jasmine D. Adamson
Jasmine D. Adamson
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 140
I added some comments to your proc, and fixed the layout, formatting, capitalization - all of which was horrifying to begin with. Pick a coding standard and then use it. I don't care what you pick, but use it consistently. I've shown you the "capitalize all keywords" coding style, which I personally hate, but I'm making the point that if it's consistent, even if you hate that style, it's more readable. Also, be careful about your tabs and what you put on each line. DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen. Anyway, the comments explain how the procedure works. This is not a very complicated proc, but the horrible style was making it very hard to read.


CREATE PROCEDURE dbo.getNotificateProduct
   @search VARCHAR(1000), -- multi-values ('value1;value2;value3;')
   @store VARCHAR(50),
   @lan VARCHAR(2) -- only 'FI' and 'EN' are valid here
AS   

DECLARE @haku VARCHAR(50) --loop variable to hold single values from @search

-- table for building result set
DECLARE @temp TABLE (ParameterCaption VARCHAR(100), ParameterValue VARCHAR(100))

--if the incoming string doesn't end with a ; then add one
IF SUBSTRING(@search, LEN(@search),1)<>';'
SET @search = @search + ';'

--initialize the loop control variable - this is a "chopping" loop,
--where each iteration consumes a portion of the string and you continue
--until there's no more string to consume (when a semicolon can not be found)
--this is not written in the normal way - it would be better to update a position
--variable, than to continuously update the value of a potentially really long string
WHILE CHARINDEX(';',@search)>0 BEGIN
   --grab everything from the beginning of the string to the first semicolon,
   --removing leading spaces and converting to upper-case. This is the search string.
   SET @haku = UPPER(LTRIM(CAST(SUBSTRING(@search,0, CHARINDEX(';',@search)) AS VARCHAR(50))))

   --load rows into the temp table for the current search string,
   --using the proper column for the language - two separate queries would actually be faster here
   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 --this is your main table
   INNER JOIN FactStoreQualityNotification --this is your join (right) table
         ON DimCubeProduct.ProductKey = FactStoreQualityNotification.ProductKey -- a JOIN condition
         AND CHARINDEX(CAST(FactStoreQualityNotification.StoreKey AS VARCHAR(5)),@store) > 0 -- another JOIN condition
   WHERE (@haku = '') OR -- where clause in the form of "A OR (B OR C)" - do you need the parens?
      ((@lan='FI' AND CHARINDEX(@haku, UPPER(Material_FI)) > 0)
      OR
      (@lan='EN' AND CHARINDEX(@haku, UPPER(Material_EN)) > 0))
   ORDER BY ProductID

   --chop off the 'front' of the string before we re-iterate - this is the "update the loop control variable" step
   SET @search = SUBSTRING(@search, CHARINDEX(';',@search)+1, LEN(@search) - 0)
END

SELECT DISTINCT * FROM @temp --wrong - you created the columns up at the top, you can't name them?! Don't use *
--Also, if you loaded the correct data into the temp table you would gain performance by eliminating Distinct keyword



For the inner portion of the loop, I would re-write that to have an "IF @lan='FI'" if-else block, and have a query for each language separately. For the 'FI' language, it would look like this...


INSERT INTO @temp
SELECT distinct Material_FI,
   '[Material DIM].[Material FI].&['+ CAST([DimCubeProduct].[ProductKey] AS VARCHAR(50))+ ']'
FROM DimCubeProduct dcp --this is your main table
INNER JOIN FactStoreQualityNotification fsqn --this is your join (right) table
      ON dcp.ProductKey = fsqn.ProductKey -- a JOIN condition
      AND CHARINDEX(CAST(fsqn.StoreKey AS VARCHAR(5)),@store) > 0 -- another JOIN condition
WHERE (@haku = '')
   OR (CHARINDEX(@haku, UPPER(Material_FI)) > 0)
ORDER BY ProductID



This is a query which will be much easier to optimize. Since the old "CASE" portion caused different columns to be retrieved, covering indexes and other issues could cause the optimizer to pick a bad plan. With both queries separated, the optimizer can pick different plans for each one, and that should improve the performance overall. The old query isn't always bad, but it's bad more often than the new style I'm proposing. I used table aliases to make the join conditions easier to read.
Jasmine D. Adamson
Jasmine D. Adamson
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 140
You should probably thank me for that...
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997

DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen.


Actually begin and end serve a VERY valid purpose within a proc. They very clearly define the beginning and the end. Yes it probably appears as a little overkill but it forces you to indent the code that is the body of the proc making it more clear in a large script what is what.

_______________________________________________________________

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)
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
Sean Lange (2/11/2013)

DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen.


Actually begin and end serve a VERY valid purpose within a proc. They very clearly define the beginning and the end. Yes it probably appears as a little overkill but it forces you to indent the code that is the body of the proc making it more clear in a large script what is what.




That's FALSE. Since you can have an unlimited number of BEGIN/END combinations in a proc, they clearly don't define the beginning and the end of the proc, even if you want them to (and you can't prevent someone from later adding code before the first BEGIN or after the last END, which is reason enough to NEVER consider "BEGIN" and "END" as "defining the beginning/end of a proc").

The block created by BEGIN/END adds restrictions on variable use, etc., outside that block, so you're just inviting errors with an unnecessary BEGIN/END.

Furthermore, indenting unconditional code in a proc makes no sense and wastes space. The required:
GO
CREATE PROCEDURE
within a script full of procs will inherently delineate the start and end of procs. Too many people over-indent as it is, making the code very hard to read.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44990 Visits: 39875
Sean Lange (2/11/2013)

DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen.


Actually begin and end serve a VERY valid purpose within a proc. They very clearly define the beginning and the end. Yes it probably appears as a little overkill but it forces you to indent the code that is the body of the proc making it more clear in a large script what is what.



BWAA-HAAA!!! That's precisely why I don't use it. I hate to be forced to do anything and I can't see burning the horizontal space by indenting virtually the whole proc by one tab.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jasmine D. Adamson
Jasmine D. Adamson
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 140
Sean Lange (2/11/2013)

DON'T use things you don't need, like the BEGIN and END for the proc itself - completely unneeded and doing nothing but cluttering up the screen.


Actually begin and end serve a VERY valid purpose within a proc. They very clearly define the beginning and the end. Yes it probably appears as a little overkill but it forces you to indent the code that is the body of the proc making it more clear in a large script what is what.



I find no reason to indent the first level of a proc. All that does is eat screen space.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search