How to write store Procedure

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

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

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

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

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

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

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

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