Replacing temp table

[krotha]

Hi
I want to increment primary key field value and insert into table, for that I am using a temp table and getting the incremented value and passing into the output parameter. This works fine, but With out using a temp a table how do I pass this incremented value into a variable and use this variable to insert into a table?

CREATE procedure test_proc
@table_name nvarchar(255),
@fld_name nvarchar(255),
@param_output nvarchar(255) output
as
declare @sql nvarchar(4000)
set @sql = 'insert into temp(id) select max('+@fld_name+')+1 from '+@table_name
Execute (@sql)
select @param_output =id from temp
set @sql = null
set @sql='insert into ' +@table_name+'('+@fld_name+')
values('+@param_output+')'
Execute(@sql)
delete temp
return @param_output
GO

 

[krotha]

Can someone please give me a hint without using a temp table.

Thanks a lot.

 

[tlbroadbent]

Use sp_executesql to execute the dynamic SQL and return a value.

CREATE procedure test_proc
@table_name nvarchar(255),
@fld_name nvarchar(255),
@param_output int output
as

declare @sql nvarchar(4000)

set @sql = 'select @param_output=max(' + @fld_name + ')+1
from ' + @table_name

exec sp_executesql @sql,N'@param_output int output',@param_output output

set @sql='insert into ' +@table_name+'('+@fld_name+')
values('+@param_output+')'

Execute(@sql)

Return

GO

NOTE: Don't use RETURN to return the value of an output parameter. Defining the parameter as OUTPUT will cause the value to be returned in the variable itself. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[krotha]

Thanks a lot a Terry. This works for me.