Convert Int to String in Stored Procedure in SQL Server

by

In this SQL Server tutorial, you will learn how to convert int to string in stored procedure in sql server.

Here, I will explain how to create a stored procedure using the CONVERT() and CAST() functions. Also, I explain to you some of the scenarios where you will required to convert int to string.

SQL Server Convert Int to String in Stored Procedure

Before proceeding to convert int to string in the stored procedure, you need to know ‘Why do we need to create a stored procedure for converting int to string?’.

There can be several scenarios in which you will need to convert int to string based on the requirements listed below.

  • Sometimes, you have to display the data in a particular way; in that case, you may need to convert an integer value to a string.
  • Maybe you are working with external systems that want data in the string format; in that case, you will need to convert the int to a string before sending the data to that system
  • When you run dynamic queries, sometimes these queries accept the parameter value as a string value; in this case, conversion from integer to string is necessary.
  • Suppose you need to generate reports that should present information in a meaningful way. You have some data in the integer form, and reports need data in string form; in that case, you will need to convert the integer to string.

There can be many more reasons to convert int to a string that totally depends on the requirement.

So here, you will create a stored procedure that accepts the integer value and converts that value into a string value. So, let’s learn with some practical examples.

SQL Server Convert Int to String in Stored Procedure using CONVERT() function

The CONVERT() function in SQL Server changes the data type of the given value into another data type. So here, you will learn to create a stored procedure to convert int to a string using the CONVERT() function.

Use the below query to create an IntToString stored procedure.

CREATE PROCEDURE IntToString
@IntValue INT,
@OutputValue NVARCHAR(60) OUTPUT
AS
BEGIN

	SET @OutputValue = CONVERT(NVARCHAR(60), @IntValue);

END;
Convert Int to String in Stored Procedure in SQL Server

In the stored procedure, IntToString is the name of the stored procedure with input @IntValue variable of type INT(integer), and the output @OutputValue is the output parameter of type NVARCHAR(50) that is going to store the converting string value.

After that, within BEGIN and END, the new value for variable @OutputValue, and the new value is the converted string. The CONVERT() function takes the value within @Intvalue and converts it into the string, then assigns the string value to the @OutputValue.

After creating the IntToString stored procedure, use the below query to call the stored procedure with integer value 42.

DECLARE @ResultString NVARCHAR(60);

EXEC IntToString @IntValue = 42, @OutputValue = @ResultString OUTPUT;

SELECT @ResultString;
SQL Server Convert Int to String in Stored Procedure using CONVERT() function Call Procedure

In the above query, declare the @ResultString variable of type NVARCAHR(60), and this variable will store the output that is returned by the stored procedure.

Then, call the IntToString stored procedure using the EXEC statements and pass the parameter value as 42 using @IntValue = 42.

Then specify that the procedure returns the value, which is the OUTPUT, and the returned output should be stored in the @ResultString variable.

When you execute or call the IntToString procedure, it returns the output as 42, which is a converted string that you can see in the above output.

SQL Server Convert Int to String in Stored Procedure using CAST() function

The CAST() also converts or changes the data type of the given value to another data type. So here, you will learn how to create a stored procedure using the CAST() function.

So use the below query to create the stored procedure Cast_IntToString.

CREATE PROCEDURE Cast_IntToString
@IntValue INT,
@OutputValue VARCHAR(40) OUTPUT
AS
BEGIN

	SET @OutputValue = CAST(@IntValue as VARCHAR(40));

END;
SQL Server Convert Int to String in Stored Procedure using CAST() function

In the stored procedure, Cast_IntToString is the name of the stored procedure with input @IntValue variable of type INT(integer), and the output @OutputValue is the output parameter of type VARCHAR(40) that will store converting string value.

After that, within BEGIN and END, assign the new value to the variable @OutputValue, using the SET keyword, and the new value is the converted string. The CAST() function takes the value within @Intvalue and changes it into the string, then assigns the string value to the @OutputValue.

Again, call the Cast_IntToString stored procedure with the integer value 102.

DECLARE @ResultString NVARCHAR(60);

EXEC Cast_IntToString @IntValue = 102, @OutputValue = @ResultString OUTPUT;

SELECT @ResultString;
SQL Server Convert Int to String in Stored Procedure using CAST() function Call Procedure

If you look at the above query again, declare the @ResultString variable of type NVARCAHR(60), which will store the output that is returned by the stored procedure.

Then, call the Cast_IntToString stored procedure using the EXEC statements and pass the parameter value as 102 using @IntValue = 102.

Then, store the returned output in the @ResultString variable. After calling or executing the Cast_IntToString procedure, it returns the output as 102, the converted string.

Conclusion

In this SQL Server tutorial, you learned how to convert int to string in a stored procedure in SQL Server and learned about two methods CONVERT() and CAST(); using this method, you created a store procedure that converts int to string.

You may also like: