How to write store Procedure

Posted by Gow.net under Sql Server on 2/16/2012 | Points: 10 | Views : 1169 | 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


Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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] [MVP] Gold | Points: 25

Up
0
Down


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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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: perfectchourasia-9163 on: 7/4/2012 [Member] Starter | Points: 25

Up
0
Down
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