how to use stored procedure argument with like operator
-
i have this stored procedure in database.
ALTER PROCEDURE dbo.SearchForASpecificDocumentByFromBeginsWith ( @SenderName nvarchar(50) ) AS Select * from MyTable Where (SenderName like 'SenderName%') RETURNhow can i use the argument (with the like operator) i am passing into this? -
i have this stored procedure in database.
ALTER PROCEDURE dbo.SearchForASpecificDocumentByFromBeginsWith ( @SenderName nvarchar(50) ) AS Select * from MyTable Where (SenderName like 'SenderName%') RETURNhow can i use the argument (with the like operator) i am passing into this? -
i have found the solution. here it is.
ALTER PROCEDURE dbo.SearchForASpecificDocumentByFromBeginsWith ( @SenderName nvarchar(50) ) AS Select * from MyTable Where (SenderName LIKE + @SenderName + '%') RETURNThat can't work. You have too many + operators. The + between LIKE and @SenderName should be removed.
*Developer Day Scotland - Free community conference *Colin Angus Mackay's Blog *Latest Scottish Developers Newsletter
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.
-
That can't work. You have too many + operators. The + between LIKE and @SenderName should be removed.
*Developer Day Scotland - Free community conference *Colin Angus Mackay's Blog *Latest Scottish Developers Newsletter
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Remember that the force of gravity can go up as well as down.
if you need too many operators you must use dynamic variable for example: -- this stored procedure used to search create PROCEDURE [dbo].[SP__Find_Country] ( @chv_Country_name varchar(150)=null, @Calling_no varchar(5)=null, @Short_Name varchar(3)=null ) AS set nocount on Declare @Condition varchar(8000) Set @condition ='' IF @chv_Country_name is not null set @Condition=' co_name like ''' +@chv_Country_name + '%''' IF @Calling_no is Not Null and @Condition ='' set @Condition=' co_Calling_code like ''' +@Calling_no + '%''' else If @Calling_no is not Null set @Condition=@Condition +' and co_Calling_code like ''' +@Calling_no + '%''' IF @Short_Name is not null and @Condition ='' Set @Condition=' co_Short_Name like ''' +@Short_Name + '%''' else IF @Short_Name is not null Set @Condition=@Condition+' and co_Short_Name like ''' +@Short_Name + '%''' if @Condition <> '' Set @Condition=@Condition+' and co_deleted=0' print @condition if @condition ='' select * from Country where co_deleted=0 -- Updated by Nabeel Adnan Order By Country.co_name -- Updated By Mahmoud Khalil else begin Exec ('select * from Country where '+ @condition + ' Order By co_name ') print 'select * from Country where '+ @condition + ' Order By co_name ' end
-
if you need too many operators you must use dynamic variable for example: -- this stored procedure used to search create PROCEDURE [dbo].[SP__Find_Country] ( @chv_Country_name varchar(150)=null, @Calling_no varchar(5)=null, @Short_Name varchar(3)=null ) AS set nocount on Declare @Condition varchar(8000) Set @condition ='' IF @chv_Country_name is not null set @Condition=' co_name like ''' +@chv_Country_name + '%''' IF @Calling_no is Not Null and @Condition ='' set @Condition=' co_Calling_code like ''' +@Calling_no + '%''' else If @Calling_no is not Null set @Condition=@Condition +' and co_Calling_code like ''' +@Calling_no + '%''' IF @Short_Name is not null and @Condition ='' Set @Condition=' co_Short_Name like ''' +@Short_Name + '%''' else IF @Short_Name is not null Set @Condition=@Condition+' and co_Short_Name like ''' +@Short_Name + '%''' if @Condition <> '' Set @Condition=@Condition+' and co_deleted=0' print @condition if @condition ='' select * from Country where co_deleted=0 -- Updated by Nabeel Adnan Order By Country.co_name -- Updated By Mahmoud Khalil else begin Exec ('select * from Country where '+ @condition + ' Order By co_name ') print 'select * from Country where '+ @condition + ' Order By co_name ' end
Rami Said Abd Alhalim wrote:
if you need too many operators you must use dynamic variable
If I need too many operators. Why would I need too many? Why don't I just need the right number?
Rami Said Abd Alhalim wrote:
create PROCEDURE [dbo].[SP__Find_Country]
+10 Points for using stored procedures.
Rami Said Abd Alhalim wrote:
set @Condition=' co_name like ''' +@chv_Country_name + '%'''
And take twice as many away for writing code that is vulnerable to a SQL Injection attack. You should really look into sp_executesql and see how to pass parameters into dynamically built SQL.
*Developer Day Scotland - Free community conference Delegate Registration Open