Problem with EXEC in stored proc. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Problem with EXEC in stored proc.


Dear SQL wizards, Does anyone know if it is possible get a return value when using exec with a character string? The code looks something like this: create procedure LogPointQuery
[some parameters]
as
begin
declare @execstring nvarchar(1024)
declare @pname nvarchar(99)

set @execstring = ‘select PointName from ‘ + @tableName + ‘ where ID_Logpoints = 8’
set @pname = exec(@execstring)

end
My aim here, naturally, is to use the value returned from the query inside the EXEC-statement further on in the stored procedure. Is this possible? Thank you in advance, Lars
No. You cannot do that
Instead insert the values to the tables and select from that table create procedure LogPointQuery
[some parameters]
as
begin
declare @execstring nvarchar(1024)
declare @pname nvarchar(99)

set @execstring = ‘select PointName from ‘ + @tableName + ‘ where ID_Logpoints = 8’

Create table #temp (PintName varchar(100))
insert into #temp exec(@execstring)
select * from #temp


end Madhivanan Failing to plan is Planning to fail
You cannot do this with EXEC(). However, you can do this with sp_ExecuteSQL like this
USE PUBS
DECLARE @stmt nvarchar(4000)
DECLARE @rowcount INT
DECLARE @table nvarchar(255)
SET @table = ‘authors’
SELECT @stmt = ‘SELECT @count = COUNT(*) FROM ‘ + @table
EXEC sp_executesql @stmt, N’ @count INT output’, @rowcount OUTPUT
IF @rowcount > 0
BEGIN
SELECT @rowcount AS Anzahl
END
RETURN Anzahl
———–
23

Frank Kalis
SQL Server MVP
http://www.insidesql.de

Thank you for your answers! Which is more efficient, the suggestion using a temporary table from Madhivanan or using the sp_executesql stored procedure? Best regards
]]>