asked
How do I make this query to work?
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO BEGIN DECLARE @ResultsTable TABLE (ColumnName nvarchar(200), ColumnValue nvarchar(100)) DECLARE @ColumnName nvarchar(128), @SearchStr2 nvarchar(110),@TableName nvarchar(256), @TableName2 nvarchar(256) DECLARE @PolicyPrefix nvarchar(5), @PolicyID nvarchar(10), @PolicyEffDate date SET @TableName = ' ' --SET @PolicyPrefix = 'CMPMO' --SET @PolicyID = '21842' --SET @PolicyEffDate = '2009-04-01' SET @PolicyPrefix = SELECT SUBSTRING(Policy, 1, 5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'; --'CMPMO' ------notices it only the policy letter part from the search result SET @PolicyID = SELECT SUBSTRING(Policy,6,len(Policy)-5) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%'; ---'08929' ------notices it only the policy number part from the search result SET @PolicyEffDate = SELECT cast(cast(EFFDTE as varchar)as date) FROM [dbo].[DWXP050] WHERE intcov LIKE '%epl%' ----'2009-11-01' ------notices it has dashes from the search result still neet o change this to work SET @SearchStr2 = (SELECT [SystemAssignId] FROM PDCDBPRD.dbo.[CoPolicyPointer] WHERE [PolicyPrefixCd] = @PolicyPrefix AND [PolicyId] = @PolicyID AND [PolicyEffDt] = @PolicyEffDate) WHILE (@TableName IS NOT NULL) BEGIN SET @ColumnName = QUOTENAME('SystemAssignId') SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_NAME)) FROM PDCDBPRD.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_NAME) > @TableName ) SET @TableName2 = 'PDCDBPRD.dbo.' + @TableName IF @ColumnName IS NOT NULL BEGIN INSERT INTO @ResultsTable EXEC( -- 'SELECT ''' + @TableName + ''', LEFT(' + @ColumnName + ', 100) FROM ' + @TableName2 + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ''' + @SearchStr2 + '''' ) END -- END END SELECT DISTINCT ColumnName, ColumnValue FROM @ResultsTable END GO
You can set the variable values directly from the data using a few functions: create table #source (id int, POLICY varchar(10), EFFDTE varchar(8)) go insert #source values (9361343,'CMPNE08929','20091101') go DECLARE @PolicyPrefix nvarchar(5), @PolicyID nvarchar(10), @PolicyEffDate date, @PolicyEffDateFromParts date select @PolicyPrefix = left(POLICY,patindex('%[0-9]%',POLICY)-1) , @PolicyID = right(POLICY,len(POLICY)-patindex('%[0-9]%',POLICY)+1) , @PolicyEffDate = cast(EFFDTE as date) , @PolicyEffDateFromParts = datefromparts(left(EFFDTE,4),substring(EFFDTE,5,2),substring(EFFDTE,7,2)) from #source select @PolicyPrefix, @PolicyID, @PolicyEffDate, @PolicyEffDateFromParts Note that this assumes that POLICY always follows the format [Text Part][Numeric Part], i.e. you don't have values like AD46TTR566. Also, the EFFDTE column should be directly castable to datetime, but you might encounter problems with the incorrect and nonsensical way that the americans order their date elements, depending on the source of your data and the region settings on your server. So I've also shown another way to calculate the date using the new datefromparts function in 2012.
15 People are following this question.
