September 13, 2006 at 1:05 pm
Need to generate a dynamic SQL statement.. The following SQL is giving me the error msg:
Msg 245, Level 16, State 1, Line 10
Syntax error converting the varchar value '' Where C_Account.RunID =' to a column of data type int.
BEGIN
SET NOCOUNT ON;
DECLARE @RunID INT,
@sql1 varchar (255),
@sql2 varchar (255),
@sql3 varchar (255),
@SQLStatement nvarchar(2000),
@BB varchar (255),
@PID varchar (255)
SET @sql1 = 'Update c_account SET MSA_BB_FootNote ='+Char (39)
SET @sql2 = char(39)+' Where C_Account.RunID =' + @RunID
SET @sql3 = 'AND c_account.account_ID ='
DECLARE c1 CURSOR static for
SELECT BBFootNote,LEFT(PortfolioID,6) FROM Core.dbo.tempBBrecords
OPEN c1
FETCH next FROM c1 INTO @BB,@PID
WHILE @@Fetch_Status = 0
Begin
SET @SqlStatement = @Sql1 + @BB + @sql2 + @sql3 + char(39)+ @PID+char(39)
EXEC sp_executesql @SQLStatement
FETCH next FROM c1 INTO @BB,@PID
END
CLOSE c1
DEALLOCATE c1
END
September 13, 2006 at 1:16 pm
Can you try this:
DECLARE @RunID INT,
@sql1 varchar (255),
@sql2 varchar (255),
@sql3 varchar (255),
@SQLStatement nvarchar(2000),
@BB varchar (255),
@PID varchar (255)
SET @sql1 = 'Update c_account SET MSA_BB_FootNote ='''
select @sql1
SET @sql2 = ' Where C_Account.RunID =' +
select @sql2
SET @sql3 = 'AND c_account.account_ID ='
select @sql3
select @Sql1 + @BB + @sql2 + @sql3 + char(39)+ @PID+char(39)
Or Try this:
DECLARE @RunID INT,
@sql1 varchar (8000)
DECLARE c1 CURSOR static for
SELECT 'Update c_account SET MSA_BB_FootNote =''' + ltrim(rtrim(BBFootNote)) +
' Where C_Account.RunID =' + Cast(isnull(@RunID,0) as varchar(10))
+ ' AND c_account.account_ID =' + Cast(isnull(LEFT(PortfolioID,6),0) as varchar(10))
FROM Core.dbo.tempBBrecords
OPEN c1
FETCH next FROM c1 INTO @sql1
WHILE @@Fetch_Status = 0
BEGIN
EXEC sp_executesql @SQLStatement
FETCH next FROM c1 INTO @sql1
END
CLOSE C1
DEALLOCATE C1
Hope this helps
Thanks
Sreejith
September 13, 2006 at 1:26 pm
neither of these suggestions were syntacticly correct nor did they work..
September 13, 2006 at 1:42 pm
Try this. I have commented Execute statement for you to see the sql before running it. Hope this helps.
BEGIN
SET NOCOUNT ON;
DECLARE @RunID INT,
@sql1 varchar (255),
@sql2 varchar (255),
@sql3 varchar (255),
@SQLStatement nvarchar(2000),
@BB varchar (255),
@PID varchar (255)
SET @sql1 = 'Update c_account SET MSA_BB_FootNote = '''
SET @sql2 = ''' Where C_Account.RunID =' + Cast(@RunID as varchar(15))
SET @sql3 = ' AND c_account.account_ID ='''
DECLARE c1 CURSOR static for
SELECT BBFootNote,LEFT(PortfolioID,6) FROM Core.dbo.tempBBrecords
OPEN c1
FETCH next FROM c1 INTO @BB,@PID
WHILE @@Fetch_Status = 0
Begin
SET @SqlStatement = @Sql1 + @BB + @sql2 + @sql3 + @PID+ ''''
select @SqlStatement
--EXEC sp_executesql @SQLStatement
FETCH next FROM c1 INTO @BB,@PID
END
CLOSE c1
DEALLOCATE c1
END
Thanks
Sreejith
September 13, 2006 at 1:59 pm
Resolution -- the concatenation of VARCHAR and INT together caused the error. I was able to resolve this via the use of the "str" implicit conversion. thanks "DUDE".
SET @sql1 = 'Update c_account SET MSA_BB_FootNote ='+Char (39)
SET @sql2 = char(39)+' Where C_Account.RunID = '
SET @sql3 = ' AND c_account.account_ID ='
DECLARE c1 CURSOR static for
SELECT BBFootNote,LEFT(PortfolioID,6) FROM Core.dbo.tempBBrecords
OPEN c1
FETCH next FROM c1 INTO @BB,@PID
WHILE @@Fetch_Status = 0
Begin
SET @SqlStatement = @Sql1 + @BB + @sql2 + str(@RUNID) + @sql3 + char(39)+ @PID+char(39)
EXEC sp_executesql @SQLStatement
FETCH next FROM c1 INTO @BB,@PID
end
CLOSE c1
DEALLOCATE c1
END
GO
"Novice's should refrain from supplying idiotic comments - it's a poor reflection on their wisdom"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply