execute sp_executesql

[hlybbi]

set @tempTable = 'SELECT sum(' + @tempTable + ') '
set @tempTable = @tempTable + 'FROM #ReturnStada '
set @tempTable = @tempTable + 'WHERE RoomType<>''Allotments left'''
set @tempTable = @tempTable + ' AND RoomType<>''Free rooms'''

execute sp_executesql @tempTable

can anybody help me to get the value of this select sum into this variable @Tempallvalue

doesnt work
set @Tempallvalue = execute sp_executesql @tempTable

Best regards Hlynur

http://www.xodus.net

 

[TheJon]

I have found using sp_executsql to be a bit difficult, but here is an example:

declare @tempTable nvarchar(200)
set @tempTable = 'SELECT sum(' + @tempTable + ') '
set @tempTable = @tempTable + 'FROM #ReturnStada '
set @tempTable = @tempTable + 'WHERE RoomType<>''Allotments left'''
set @tempTable = @tempTable + ' AND RoomType<>''Free rooms'''
declare @params nvarchar(100)
set @params = N'@tempallvalue_out int OUTPUT'
exec sp_executeSQL @tempTable, @params, @tempallvalue_out = @Tempallvalue OUTPUT

print @Tempallvalue

I hope it works for you.

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[hlybbi]

thang you for your answer TheJon,
i Will try this out but what i did to work around this problem was to make an #temp table and put the answer in that temp and then read out the resoults

set @tempTable = 'Insert into #temp(temp)'
set @tempTable = @tempTable +'SELECT sum(' + @tempTable + ') '
set @tempTable = @tempTable + 'FROM #ReturnStada '
set @tempTable = @tempTable + 'WHERE RoomType<>''Allotments left'''
set @tempTable = @tempTable + ' AND RoomType<>''Free rooms'''

but your way is mutch better and i will remember this the next time i get into this problem.


Best regards Hlynur

http://www.xodus.net