Hi All,
The problem is about cross reference.
1. I have a third party cross reference store procedure SimpleXTab
CREATE PROCEDURE [dbo].[SimpleXTab2]
@.XField varChar(50),
@.XTable varChar(100),
@.XWhereString varChar(250),
@.XFunction varChar(10),
@.XFunctionField varChar(50),
@.XRow varchar(300),
@.ResultTable varchar(100)
AS
Declare @.SqlStr nvarchar(4000)
Declare @.tempsql nvarchar(4000)
Declare @.SqlStrCur nvarchar(4000)
Declare @.col nvarchar(100)
set @.SqlStrCur = N'Select [' + @.XField + '] into ##temptbl_Cursor from [' + @.XTable + '] ' + @.XWhereString + ' Group By [' + @.XField + ']'
/* select @.sqlstrcur */
exec sp_executesql @.sqlstrcur
declare xcursor Cursor for Select * from ##temptbl_Cursor
open xcursor
Fetch next from xcursor
into @.Col
While @.@.Fetch_Status = 0
Begin
set @.Sqlstr = @.Sqlstr + ", "
set @.tempsql = isnull(@.sqlstr,'') + isnull(@.XFunction + '( Case When ' + @.XField + " = '"+@.Col +
"' then [" + @.XFunctionField + "] Else 0 End) As [" + @.Col + "]" ,'')
set @.Sqlstr = @.tempsql
Fetch next from xcursor into @.Col
End
/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */
set @.tempsql = 'Select ' + @.XRow + ', ' + @.Sqlstr + 'into '+@.ResultTable+' From ' + @.XTable +
@.XWhereString + ' Group by ' + @.XRow
print @.tempsql
set @.Sqlstr = @.tempsql
Close xcursor
Deallocate xcursor
set @.tempsql = N'Drop Table ##temptbl_Cursor'
exec sp_executesql @.tempsql
print @.tempsql
/* Select @.Sqlstr as [mk], len(@.sqlstr) as [leng] */
print @.sqlstr
exec sp_executesql @.Sqlstr
if @.@.rowcount = 0 select 'No Records found'
GO
2. I've use this store procedure for many cross reference successfully. But this time my cross reference value (resultcode) is a varchar which cannot be convert to int or decimal in sql, Probably, you've noticed that the fourth parameter is a function.
how can i modify SimpleXtab to avoid using math function but still can generate cross reference.
exec simplextab2 'Sequence','##tbltempreport',' ','sum','resultcode','Parameter' ,'dbo.resultcodetable'
Many Thanks!
no replying. I didn't describe clearly?
|||What did you want it to do with multiple values? If there is only one value per row/column, use MAX or MIN.
