Click here to Skip to main content
15,916,600 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello everybody,
I try to write a stored procedure for a couple of days, but I can't because I don't understand why show me the error every time. I am wrong, but I have no idea how I write some script right.

This is my procedure
SQL
CREATE PROCEDURE SelectCustomers1_13
	@CustomerID int
AS
	IF @CustomerID IS NOT NULL
		SELECT CONVERT(varchar(50),CustomerID) AS CustomerID, CompanyName FROM Customers
		WHERE CustomerID like @CustomerID + '%'
	ELSE
		SELECT * FROM Customers


When I pass some parameter to procedure, have to display these records that have CustomerID that has first number passed parameter. For instance If my table has 11 rows and I pass number 1, Results will be 1,10,11 records from my table.

Unfortunately this script display some error, when execute it.

Conversion failed when converting the varchar value '%' to data type int.

I am new in sql and I don't understand many things, I will be very happy If somebody write me and give me an answare. Thank you.
Posted
Updated 27-Apr-12 20:12pm
v2

If you would like to get customer by his id, try this:
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		deniBG
-- Create date: 2012-04-28
-- Description:	Get customer by CustomerID
-- =============================================
CREATE PROCEDURE SelectCustomerByID 
	-- Add the parameters for the stored procedure here
	@cit int = 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 *
	FROM Customers 
	WHERE CustomerId = @cit
END
GO


but if you would like to select customers by ID which id's starting with some value, for ex.: 1*, 10*, 123*
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		deniBG
-- Create date: 2012-04-28
-- Description:	Get customer by CustomerID
-- =============================================
CREATE PROCEDURE SelectCustomersLikeID 
	-- Add the parameters for the stored procedure here
	@cit int = 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 *
	FROM Customers 
	WHERE CONVERT(VARCHAR, CustomerId) Like CONVERT(VARCHAR, @cit) + '%'
END
GO


In both cases you should call these sp:
SQL
DECLARE @RC int
DECLARE @cit int

--Set input parameter value 
SET @cit = 1

EXECUTE @RC = [A_TEST].[dbo].[SelectCustomerByID] @cit

EXECUTE @RC = [A_TEST].[dbo].[SelectCustomersLikeID] @cit


In the first case, you'll get only one result. In the second case, you'll get as many records, as many CustomerID starts with '1'.

I hope it will be helpful.
 
Share this answer
 
Change:
SQL
SELECT CONVERT(varchar(50),CustomerID) AS CustomerID, CompanyName FROM Customers
WHERE CustomerID like @CustomerID + '%'

To
SQL
SELECT CustomerID, CompanyName FROM Customers
WHERE CustomerId LIKE CONVERT(VARCHAR(50), CustomerID)  + '%'
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900