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
http://www.insidesql.de

---

Hi FrankKalis
Can you just elaborate INSERT INTO …EXEC…

---

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

---

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.

---

]]>