<p>I have a sproc that returns a single line and column with a text, I need to set this text to a variable, something like:</p>

<pre><code>declare @bla varchar(100)
select @bla = sp_Name 9999, 99989999, 'A', 'S', null
</code></pre>

<p>but of course, this code doesn't work...</p>

<p>thanks!</p>

If the stored procedure is returning a single value you could define one of the parameters on the stored procedure to be an OUTPUT variable, and then the stored procedure would set the value of the parameter
```
```
CREATE PROCEDURE dbo.sp_Name
    @In INT,
    @Out VARCHAR(100) OUTPUT

AS
BEGIN
    SELECT @Out = 'Test'
END
GO
```
```
And then, you get the output value as follows
```
```
DECLARE @OUT VARCHAR(100)
EXEC sp_name 1, @Out OUTPUT
PRINT @Out
```
```