June 17, 2013 at 4:16 pm
Hello,
I hopefully can explain my self, first the code 🙂
-- Declare the return variable here
DECLARE @sql int,@tablename varchar(max) = 'POHeader',@Results int
set @sql = 'select max(PROGRESS_RECID) from Epicor_SGI.dbo.'+@tablename
-- Add the T-SQL statements to compute the return value here
exec @sql
set @Results = @sql
print @results
-- Return the result of the function
RETURN @Results
so what I would like is from the @Sql which runs a sql script in varchar, to return the results of the max row into int, so i can return it from a function and use it for another database for auditing purposes... sadly its not converting... how can i accomplish this, again sorry if it doesnt make sense, not sure if you need a sample database let me know as i can easily just put out a simple 2 column table.
thanks in advance
June 17, 2013 at 4:39 pm
In SQL you can't build dynamic SQL and execute it as part of a function. So as far as that is concerned you can't.
As far as the dynamic sql you have other issues. You can execute the statement by using an EXEC ( @sql ) or sp_execute.
I think you might want to explain better what you want to accomplish.
CEWII
June 17, 2013 at 4:47 pm
One way is to put the result into an already created temp table and select it after the 'exec'.
DECLARE @sql varchar(max),@tablename varchar(max) = 'sys.syscolumns', @Results bigint
create table #temp2 (maxid bigint null)
set @sql = 'insert #temp2 select max(ID) from '+ @tablename
-- Add the T-SQL statements to compute the return value here
exec (@sql)
select @Results = [maxid] from #temp2
select @Results
Note the change of @sql to varchar(max).
June 17, 2013 at 4:51 pm
Sorry, I may have misread the OP. You can't, as has been previously stated, do the dynamic SQL inside a function. Does this have to be in a function?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply