Creating a stored procedure
I would like to create a simple stored procedure in SQL Server 2005, where there is one argument used as a search string for a LIKE clause.
For example to run it I would like to type
spFindThis 'partoffilename'
Where the search string would get inserted into a query such as
SELECT * FROM TheTable WHERE SearchedField LIKE '%@TheArgument%'
In the code snippet I will describe further what I don't understand....
For example to run it I would like to type
spFindThis 'partoffilename'
Where the search string would get inserted into a query such as
SELECT * FROM TheTable WHERE SearchedField LIKE '%@TheArgument%'
In the code snippet I will describe further what I don't understand....
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO
-- THIS IS OBVIOUSLY WHAT IS CREATED BY DEFAULT WHEN I WANT TO MAKE A STORED PROCEDURE BUT I DON'T REALLY UNDERSTAND WHERE TO PUT THE ARGUMENTS OR WHAT TO PUT IN THE <Procedure_Name, sysname, ProcedureName> BIT. CAN ANYONE HELP? THANKS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes this made it much easier, and the good thing about it is that I can see what it wrote in to the code in order to do it manually in future and get abetter understanding.
CREATE PROC dbo.YourProcedureName
@TheArgument nvarchar(100)
AS
SET NOCOUNT ON
EXEC dbo.sp_executesql
N'SELECT * FROM TheTable WHERE SearchedField LIKE ''%'' + @param + ''%''',
N'@param nvarchar(100)',
@param = @TheArgument
GO