Convert int to varchar

Question

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 27-Apr-12 20:11pm

deniBG

Updated 27-Apr-12 20:12pm

Prasad_Kulkarni

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2012-04-27T21:09:00

Solution 1

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)  + '%'

Posted 27-Apr-12 21:09pm

OriginalGriff

Maciej Los · Answer 2 · 2012-04-27T21:11:00

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.