Select @Variable = Exec StoredProcedureName | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Select @Variable = Exec StoredProcedureName

Is it possible to store the output of SP into variable directly? Like I want to store the result of following SP into variable. DECLARE @CodeLabel_Title VarChar(255)
Select @CodeLabel_Title = Exec ui_CMODcodes_sel1 ‘BTITLE’, 14, 1015,6860,173346
Print @CodeLabel_Title This is giving me an error " Incorrect syntax near the keyword ‘Exec’. "
Is there any another command for this?
It is possible if you have an OUTPUT parameter in SP but is it possible without OUTPUT parameter?

AFAIK, this is not possible. However, you can either use an OUTPUT parameter or INSERT INTO …EXEC… —
Frank Kalis
SQL Server MVP

Hi FrankKalis
Can you just elaborate INSERT INTO …EXEC…

CREATE TABLE cmdshell (line VARCHAR(260))
INSERT INTO cmdshell EXEC master.dbo.xp_cmdshell ‘dir c: /B’
DROP TABLE cmdshell Instead of master.dbo.xp_cmdshell you would rather use your sproc. —
Frank Kalis
SQL Server MVP

Thanks FrankKalis. But it is working slower than the normal one. Any other solution for this.
Can you create a function instead of procedure?
Thanks Frank it is working fine in SP. May be I was doing something wrong in my first test. CREATE TABLE TempCodeLabel (CodeLabel_Title VarChar(255))
INSERT INTO TempCodeLabel Exec ui_CMODcodes_sel1 ‘BTITLE’, 14, 1015,6860,173346
SELECT * FROM TempCodeLabel
DROP TABLE TempCodeLabel Thanks once again.