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

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

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

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

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

Also refer this
http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

http://www.codeproject.com/Articles/38682/Overview-of-SQL-Server-Stored-Procedure

http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx

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

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

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

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

ER sandeep chourasia
sandeepchrs@yahoo.com (on facebook)
http://sandeep-chourasia.blogspot.com/