November 3, 2008 at 4:55 am
I am creating a dynamic sql in my stored proc and executing it with an executesql.
But whenever i call this proc i get this weird error:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'Ext'.
I went to the microsoft site i found that replacing double quotes by two single quote would word but phew...it did not work for me.
Can any one help?
Below is the code for reference:
DECLARE @PartSurrName varchar(20), @ArrayString nvarchar(2000)
SET NOCOUNT ON
CREATE TABLE #temp1_PBMVR
(
ID int, Type char(2) NULL,ExtractDate datetime, Col varchar(20),jan varchar(15), feb varchar(15), mar varchar(15), apr varchar(15),
may varchar(15),jun varchar(15), jul varchar(15), aug varchar(15),
sep varchar(15), oct varchar(15), nov varchar(15), [dec] varchar(15)
)
-- Outer cursor loops through all the PartSurr with-profits columns
SELECT name INTO #PartSurrCol FROM syscolumns
WHERE ID = (SELECT OBJECT_ID('MVR_prubond_data'))
AND (name LIKE 'PartSurr_day%' OR name LIKE 'PartSurr_off%' OR name LIKE 'PartSurr_pen%'
OR name LIKE 'PartSurr_sf%' OR name LIKE 'PartSurr_tb%')
DECLARE PartSurrCur CURSOR FOR
SELECT name FROM #PartSurrCol
OPEN PartSurrCur
FETCH NEXT FROM PartSurrCur
INTO @PartSurrName
WHILE @@FETCH_STATUS =0
BEGIN
-- Inner Cursor loops through IDs and unpacks arrays
SELECT @ArrayString = 'DECLARE @ID int, @Array varchar(150), @ExtractDate datetime, @PartSurrName varchar(20) SELECT @PArtSurrName="' + @PartSurrName + '"
DECLARE ArrayCur CURSOR FOR
SELECT ID,' + @PartSurrName + ',ExtractDate,"@PArtSurrName" FROM MVR_PruBond_Data WHERE ' + @PartSurrName + ' <>"0"
OPEN ArrayCur
FETCH NEXT FROM ArrayCur
INTO @ID, @Array,@ExtractDate, @PartSurrName
WHILE @@FETCH_STATUS =0
BEGIN
INSERT #temp1_PBMVR
SELECT @ID, NULL,@ExtractDate, "' + @PartSurrName + '", * FROM dbo.ufn_PSPWarrayUnpack(@Array)
FETCH NEXT FROM ArrayCur INTO @ID, @Array,@ExtractDate, @PartSurrName
END
CLOSE ArrayCur
DEALLOCATE ArrayCur'
EXEC sp_executesql @ArrayString
FETCH NEXT FROM PartSurrCur INTO @PartSurrName
END
CLOSE PartSurrCur
DEALLOCATE PartSurrCur
November 3, 2008 at 5:55 am
DECLARE @PartSurrName varchar(20), @ArrayString nvarchar(2000)
SET NOCOUNT ON
CREATE TABLE #temp1_PBMVR
(
ID int, Type char(2) NULL,ExtractDate datetime, Col varchar(20),jan varchar(15), feb varchar(15), mar varchar(15), apr varchar(15),
may varchar(15),jun varchar(15), jul varchar(15), aug varchar(15),
sep varchar(15), oct varchar(15), nov varchar(15), [dec] varchar(15)
)
-- Outer cursor loops through all the PartSurr with-profits columns
SELECT name INTO #PartSurrCol FROM syscolumns
WHERE ID = (SELECT OBJECT_ID('MVR_prubond_data'))
AND (name LIKE 'PartSurr_day%' OR name LIKE 'PartSurr_off%' OR name LIKE 'PartSurr_pen%'
OR name LIKE 'PartSurr_sf%' OR name LIKE 'PartSurr_tb%')
DECLARE PartSurrCur CURSOR FOR
SELECT name FROM #PartSurrCol
OPEN PartSurrCur
FETCH NEXT FROM PartSurrCur
INTO @PartSurrName
WHILE @@FETCH_STATUS =0
BEGIN
-- Inner Cursor loops through IDs and unpacks arrays
SELECT @ArrayString = 'DECLARE @ID int, @Array varchar(150), @ExtractDate datetime, @PartSurrName varchar(20) SELECT @PArtSurrName=''' + @PartSurrName + '''
DECLARE ArrayCur CURSOR FOR
SELECT ID,' + @PartSurrName + ',ExtractDate,"@PArtSurrName" FROM MVR_PruBond_Data WHERE ' + @PartSurrName + ' <>"0"
OPEN ArrayCur
FETCH NEXT FROM ArrayCur
INTO @ID, @Array,@ExtractDate, @PartSurrName
WHILE @@FETCH_STATUS =0
BEGIN
INSERT #temp1_PBMVR
SELECT @ID, NULL,@ExtractDate, ''' + @PartSurrName + ''', * FROM dbo.ufn_PSPWarrayUnpack(@Array)
FETCH NEXT FROM ArrayCur INTO @ID, @Array,@ExtractDate, @PartSurrName
END
CLOSE ArrayCur
DEALLOCATE ArrayCur'
EXEC sp_executesql @ArrayString
FETCH NEXT FROM PartSurrCur INTO @PartSurrName
END
CLOSE PartSurrCur
DEALLOCATE PartSurrCur
Failing to plan is Planning to fail
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply