Appear in Online Interview to test your skills and be ready for job!
Twitter Twitter LinkedIn YouTube Google
Be Interview ready ! | Search
Submit content and win cash & prizes monthly.
Winners

Win Prizes

Announcements
Like us on Facebook
Sponsor
Top Forums Author
Thu, 24-Apr-2014 Authors
Zenny
10
All Time Authors
Jayakumars
3930
Karthikanbarasan
3455
Chvrsri
3140

Latest members | More ...


(Statistics delayed by 5 minutes)

How to write store Procedure

Gow.net
Posted by Gow.net under Sql Server on 2/16/2012 7:14:57 AM | Points: 10 | Views : 664 | Status : [Member] | Replies : 12
How to write store Procedure For below query

select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all  select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand


gowthaman8870226416

Reply | Reply with Attachment

=> Scroll below for responses


Comments or Responses

Posted by: Swapnil on: 2/16/2012 [Member] Starter | Points: 25

Here you go.
CREATE PROCEDURE sp_myStoredProcedure

AS

select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand

Go

Thanks and Regards,
Swapnil

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Gow.net on: 2/16/2012 [Member] Starter | Points: 25

hi Swapnil sir
thanks for your replay
but how apply this procedure in my application because here i am searching 3 key words
 commands like '%l%' and commands like '%l%'or commands like '%l%' 
so how i pass 3 parameters

gowthaman8870226416

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Swapnil on: 2/16/2012 [Member] Starter | Points: 25

you can add more parameter based on your requirement however from procedure all three parameters are same so i have added 1 parameter based on your requirement.

CREATE PROCEDURE sp_myStoredProcedure

@commands char(1) = null
AS
select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%' + commands + '%' and commands like '%' + commands + '%' or commands like '%' + commands + '%' and rating !='Poor' and rating !='Not Poor'GROUP BY model,rating,brand
Go

Thanks and Regards,
Swapnil

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 6/26/2012 [Member] Starter | Points: 25

CREATE PROCEDURE sp_TestProcedure

@proid int,
@proname varchar(90),
@prodesci varchar(90)

AS
select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand

Go


Hope This will help

Regards

sriram

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 6/29/2012 [Member] Gold | Points: 25



SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.Procedure_Name
@KeyWord1 varchar(50) = null
@KeyWord2 varchar(50) = null
@KeyWord3 varchar(50) = null
AS
BEGIN
select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%@KeyWord1%' and commands like '%@KeyWord2%'or commands like '%@KeyWord3%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%@KeyWord1%' and commands like '%@KeyWord2%'or commands like '%@KeyWord3%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand


END
GO

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/29/2012 [Member] Silver | Points: 25

Posted by: CGN007 on: 6/29/2012 [Member] Silver | Points: 25

Posted by: CGN007 on: 6/29/2012 [Member] Silver | Points: 25

Posted by: CGN007 on: 6/29/2012 [Member] Silver | Points: 25

Also mark as answer,if it helps you...

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 7/3/2012 [Member] Starter | Points: 25

Create procedure ABC
as
begin
select brand,model,rating,sum(rate)as ModelRank from dellfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand union all select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback WHERE commands like '%l%' and commands like '%l%'or commands like '%l%' and rating !='Poor'and rating !='Not Poor'GROUP BY model,rating,brand

end

Regards
Muralidoss M

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 7/3/2012 [Member] [MVP] Silver | Points: 25

You can use Table type parameter...
Use <Database Name>

Go

/*Creating User Defined Table Type*/
If TYPE_ID('Rating') Is Null
CREATE TYPE Rating AS TABLE(
Rating varchar(100)
)
GO

/*Creating Stored Procedure having Two parameters*/
If OBJECT_ID('Usp_Feedback') Is Not Null
Drop Proc Usp_Feedback
Go
Create Proc Usp_Feedback
(
@Commands Varchar(10),
@Rating Rating Readonly
)
As
Begin
Set Nocount On

select brand,model,rating,sum(rate)as ModelRank from dellfeedback
WHERE commands like '%' + @Commands + '%'
and rating Not In (Select Rating From @Rating)
GROUP BY model,rating,brand
union
select brand,model,rating,sum(rate)as ModelRank from Sonyfeedback
WHERE commands like '%' + @Commands + '%'
and rating Not In (Select Rating From @Rating)
GROUP BY model,rating,brand
End
Go

/*Executing the Stored procedure*/
Declare @Rating Rating

Insert @Rating Values('Poor')
Insert @Rating Values('Not Poor')

Exec Usp_Feedback '1',@Rating
Go

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Perfect.Chourasia@Gmail.Com on: 7/4/2012 [Member] Starter | Points: 25

Above answer is correct .So update the result (Set resolved)

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://www.aspnetcodes.com/

Gow.net, if this helps please login to Mark As Answer. | Alert Moderator


Login to post response.

Comments / Responses
Select text & click toolbar to format. Formatting appears in viewmode only. HTML Tags are not allowed.
Bold Italic Underline Paragraph Title Code  Link 
 Wait ... Processing ..... please wait.

Found interesting? Add this to: