将int转换为varchar [英] Convert int to varchar

问题描述

大家好，
我尝试写存储过程几天，但是我不能，因为我不明白为什么每次都会向我显示错误.我错了，但是我不知道如何正确编写一些脚本.

这是我的程序

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

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

当我将一些参数传递给过程时，必须显示这些记录，这些记录的CustomerID具有第一个传递的参数.例如，如果我的表有11行并且传递数字1，则结果将是我的表中的1,10,11条记录.

不幸的是，该脚本在执行时显示一些错误.

将varchar值％"转换为int数据类型时，转换失败.

我是sql的新手，我不了解很多事情，如果有人给我写信并给我一个答复，我将非常高兴.谢谢.

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.

推荐答案

更改:

SELECT CONVERT(varchar(50),CustomerID) AS CustomerID, CompanyName FROM Customers
WHERE CustomerID like @CustomerID + '%'

致

To

SELECT CustomerID, CompanyName FROM Customers
WHERE CustomerId LIKE CONVERT(VARCHAR(50), CustomerID)  + '%'

如果您想通过客户ID获得客户，请尝试以下操作:

If you would like to get customer by his id, try this:

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

但是，如果您要按ID以某个值开头的ID选择客户，例如:1 *，10 *，123 *

but if you would like to select customers by ID which id''s starting with some value, for ex.: 1*, 10*, 123*

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

在这两种情况下，您都应将其称为sp:

In both cases you should call these sp:

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

在第一种情况下，您只会得到一个结果.在第二种情况下，您将获得尽可能多的记录，而许多CustomerID以"1"开头.

希望对您有所帮助.

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.

这篇关于将int转换为varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！