July 15, 2008 at 5:24 am
Hi all.
I used like operator to search my customers. But i had problem.
In sql query analyser it working well. But in my stored procedure it isnt work. Let me know the reason and how i solve it
.This is my code Stored procedure code
CREATE PROCEDURE Get_CustomerDetailsByName
@CustomerName char(30)
AS
SELECT *
FROM CUSTOMER
WHERE CUSTOMER_NAME Like '%'+ @CustomerName + '%'
GO
This was my QUERY ANALIZER tried code
declare @CustomerName as nvarchar(200)
set @CustomerName='a'
SELECT *
FROM CUSTOMER
WHERE CUSTOMER_NAME Like +'%'+ @CustomerName + '%'
Thankyou
July 15, 2008 at 5:46 am
Post your error message.
karthik
July 15, 2008 at 5:55 am
Here no error messege showing. ( i dont know how to debug stored procedure ).
Just i tried to get dataset(im using c# asp.net) . Always my dataset blank. then i checked it with queryanalizer.It has some data.
thanks
July 15, 2008 at 6:06 am
--Try This
ALTER PROCEDURE Get_CustomerDetailsByName
@CustomerName varchar(30)
AS
EXEC('SELECT *
FROM CUSTOMER
WHERE CUSTOMER_NAME Like ''%'+ @CustomerName + '%''')
July 15, 2008 at 6:17 am
No need for dynamic SQL. Seems like a panic attack to me.
ALTER PROCEDURE Get_CustomerDetailsByName
(
@CustomerName varchar(30)
)
AS
SET NOCOUNT ON
SELECT*
FROMCUSTOMER
WHERECUSTOMER_NAME LIKE '%' + @CustomerName + '%'
The differencies in your result is that on SP you have CHAR, and in QA you have NVARCHAR.
CHAR has trailing spaces.
N 56°04'39.16"
E 12°55'05.25"
July 15, 2008 at 6:19 am
July 15, 2008 at 6:29 am
July 15, 2008 at 6:31 am
I just wanted to change the CHAR into VARCHAR.
I tried to use dynamic SQL to print the query. Thats it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply