Search
Sponsor
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Forums Author
Sat, 29-Nov-2014 Authors
All Time Authors
Jayakumars
4210
Karthikanbarasan
3455
Shanky11
3150

Latest members | More ...


(Statistics delayed by 5 minutes)
 Forum Post

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 : 897 | 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

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


Found interesting? Add this to: