Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DGOBRIEN
Starting Member
1 Post |
 Posted - 2010-04-21 : 11:52:39
|
| I have this query:DECLARE @CurrentNames varchar(max)DECLARE @query varchar(max) SELECT @CurrentNames = coalesce(@CurrentNames + ',' , '') + ' [' + [Name] + '] ' FROM [LC2].[dbo].[lu_tbl_Updates] where currentupdate = 1set @query = N' select * from (SELECT distinct host.[HostID], app.Name ,host.patchstatus FROM [LC2].[dbo].[tbl_HostUpdatesCurrent] host join [LC2].[dbo].[v_hostDetailsBaseLine] hosts on host.hostid = hosts.hostid join dbo.lu_tbl_Updates app on app.uid = host.PatchID group by host.[HostID],Hosts.[HostName],app.Name,host.patchstatus ) as s PIVOT ( min(s.patchstatus) for s.Name in (' + @CurrentNames + ')) as p;' EXECUTE( @query )It runs fine and returns the expected data... Now I want to make it a function so that I can then use it in a viewI've tried some examples i've found but none seem to work right...Can someone assist me? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-21 : 23:43:56
|
| you cant use dynamic sql in a function. why dont you make it procedure instead?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
How do I make this a function?