complicated dynamic SQL statement

[jvande]

exec('UPDATE r_pdf_confidential_build_detail SET '+@businesss_Field1+'=Cast(@business_number as varchar(11)) where @business_number=@business_counter and @chronid=@chron_id')

I get the error
Must declare the variable '@business_number'
@business-number is a variable that is defined already. I think something is wrong with my syntex. Any suggestions.

 

[LogicalmanUS]

jvande,

Can you post all the code and I can take a look at it.
As before, it may be prudent to place all the syntax into a variable and then you use the print statement to ensure you are assigning the variable value, rather than the variable name, in the code itself, as the error you are getting indicates the latter.

Logicalman

 

[jvande]

here is the whole procedure. Error is
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@business_number'.
I declare all these variable below....
--insert info from a_business
Declare @CHRONID varchar(11),
@business_number varchar(11),
@YES_NO_STILL_OPERATING varchar(11),
@CHOICE_TYPE varchar(11),
@CHOICE_ACTIVITY varchar(11),
@PERCENT_OWNED varchar(11),
@ACTUAL_GROSS_RECEIPTS varchar(11),
@ACTUAL_EXPENSES varchar(11),
@ACTUAL_NET_INCOME varchar(11),
@ESTIMATED_GROSS_RECEIPTS varchar(11),
@ESTIMATED_EXPENSES varchar(11),
@ESTIMATED_NET_INCOME varchar(11),
@NET_INCOME_MANUAL_CALCULATION varchar(11),
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER varchar(11),
@CHOSEN_SCENARIO_AMOUNT varchar(11),
@business_counter varchar(1),
@businesss_Field1 varchar(30),
@businesss_Field2 varchar(30),
@businesss_Field3 varchar(30),
@businesss_Field4 varchar(30),
@businesss_Field5 varchar(30),
@businesss_Field6 varchar(30),
@businesss_Field7 varchar(30),
@businesss_Field8 varchar(30),
@businesss_Field9 varchar(30),
@businesss_Field10 varchar(30),
@businesss_Field11 varchar(30),
@businesss_Field12 varchar(30),
@businesss_Field13 varchar(30),
@businesss_Field14 varchar(30)
set @business_counter=1

declare business_pointer Cursor for
Select Cast(CHRON_ID as varchar(11)),Cast(BUSINESS_NUMBER as varchar(11)),Cast(YES_NO_STILL_OPERATING as varchar(11)),Cast(CHOICE_TYPE as varchar(11)),Cast(CHOICE_ACTIVITY as varchar(11)),Cast(PERCENT_OWNED as varchar(11)),Cast(ACTUAL_GROSS_RECEIPTS as varchar(11)),Cast(ACTUAL_EXPENSES as varchar(11)),Cast(ACTUAL_NET_INCOME as varchar(11)),Cast(ESTIMATED_GROSS_RECEIPTS as varchar(11)),Cast(ESTIMATED_EXPENSES as varchar(11)),Cast(ESTIMATED_NET_INCOME as varchar(11)),Cast(NET_INCOME_MANUAL_CALCULATION as varchar(11)),Cast(CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER as varchar(11)),Cast(CHOSEN_SCENARIO_AMOUNT as varchar(11))
from a_business
where chron_id=@chron_id
open business_pointer
fetch next from business_pointer into @CHRONID,@BUSINESS_NUMBER,@YES_NO_STILL_OPERATING,@CHOICE_TYPE,@CHOICE_ACTIVITY,@PERCENT_OWNED,@ACTUAL_GROSS_RECEIPTS,@ACTUAL_EXPENSES,@ACTUAL_NET_INCOME,@ESTIMATED_GROSS_RECEIPTS,@ESTIMATED_EXPENSES,@ESTIMATED_NET_INCOME,@NET_INCOME_MANUAL_CALCULATION,@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,@CHOSEN_SCENARIO_AMOUNT
while @@fetch_status=0
begin
Set @businesss_Field1='Business_number'+@business_counter
Set @businesss_Field2='YES_NO_STILL_OPERATING'+@business_counter
Set @businesss_Field3='CHOICE_TYPE'+@business_counter
Set @businesss_Field4='CHOICE_ACTIVITY'+@business_counter
Set @businesss_Field5='PERCENT_OWNED'+@business_counter
Set @businesss_Field6='ACTUAL_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field7='ACTUAL_EXPENSES'+@business_counter
Set @businesss_Field8='@ACTUAL_NET_INCOME'+@business_counter
Set @businesss_Field9='@ESTIMATED_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field10='@ESTIMATED_EXPENSES'+@business_counter
Set @businesss_Field11='@ESTIMATED_NET_INCOME'+@business_counter
Set @businesss_Field12='@NET_INCOME_MANUAL_CALCULATION'+@business_counter
Set @businesss_Field13='@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER'+@business_counter
Set @businesss_Field14='@CHOSEN_SCENARIO_AMOUNT'+@business_counter

print @businesss_Field1
exec('UPDATE r_pdf_confidential_build_detail SET ' +@businesss_Field1+'=Cast(@business_number as varchar(11)), '+@businesss_Field2+'=Cast(@YES_NO_STILL_OPERATING as varchar(11)), '+@businesss_Field3+'=Cast(@CHOICE_TYPE as varchar(11)),'+@businesss_Field4+'=Cast(@CHOICE_ACTIVITY as varchar(11)), '+@businesss_Field5+'=Cast(@PERCENT_OWNED as varchar(11)),'+@businesss_Field6+'=Cast(@ACTUAL_GROSS_RECEIPTS as varchar(11)), '+@businesss_Field7+'=Cast(@ACTUAL_EXPENSES as varchar(11)), '+@businesss_Field8+'=Cast(@ACTUAL_NET_INCOME as varchar (11)),'+@businesss_Field9+'=Cast(@ESTIMATED_GROSS_RECEIPTS as varchar(11)), '+@businesss_Field10+'=Cast(@ESTIMATED_EXPENSES as varchar(11)), '+@businesss_Field11+'=Cast(@ESTIMATED_NET_INCOME as varchar(11)), '+@businesss_Field12+'=Cast(@NET_INCOME_MANUAL_CALCULATION as varchar(11)), '+@businesss_Field13+'=Cast(@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER as varchar(11)), '+@businesss_Field14+'=Cast(@CHOSEN_SCENARIO_AMOUNT as varchar(11))
where @business_number=@business_counter and @chronid=@chron_id')
set @business_counter=@business_counter+1
fetch next from business_pointer into @CHRONID,@BUSINESS_NUMBER,@YES_NO_STILL_OPERATING,@CHOICE_TYPE,@CHOICE_ACTIVITY,@PERCENT_OWNED,@ACTUAL_GROSS_RECEIPTS,@ACTUAL_EXPENSES,@ACTUAL_NET_INCOME,@ESTIMATED_GROSS_RECEIPTS,@ESTIMATED_EXPENSES,@ESTIMATED_NET_INCOME,@NET_INCOME_MANUAL_CALCULATION,@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,@CHOSEN_SCENARIO_AMOUNT
end
close business_pointer
deallocate business_pointer

 

[LogicalmanUS]

jvande,

Sorry for the delay, Man Utd vs Southampton was on TV!!

Right, back to the problem in hand ...
In the past I have found that when I declare a variable, I must use it EXACTLY as declared, e.g. case sensitive, on some systems.

I noticed that you declare @CHRONID in uppercase, but use in lower case and conversely the same for @business_number.

Try those items above, whilst I create a table in order to test the sp out more fully.

Logicalman

 

[LogicalmanUS]

JVande,

I have now tested your sp, and found that making the changes as per my previous reply, seems to have done the trick.

The code I used is:

CREATE PROCEDURE [dbo].[sp_JVande]


AS
/*
here is the whole procedure. Error is
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@business_number'.
I declare all these variable below....
*/

--insert info from a_business
Declare
@CHRONID varchar(11),
@business_number varchar(11),
@YES_NO_STILL_OPERATING varchar(11),
@CHOICE_TYPE varchar(11),
@CHOICE_ACTIVITY varchar(11),
@PERCENT_OWNED varchar(11),
@ACTUAL_GROSS_RECEIPTS varchar(11),
@ACTUAL_EXPENSES varchar(11),
@ACTUAL_NET_INCOME varchar(11),
@ESTIMATED_GROSS_RECEIPTS varchar(11),
@ESTIMATED_EXPENSES varchar(11),
@ESTIMATED_NET_INCOME varchar(11),
@NET_INCOME_MANUAL_CALCULATION varchar(11),
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER varchar(11),
@CHOSEN_SCENARIO_AMOUNT varchar(11),
@business_counter varchar(1),
@businesss_Field1 varchar(30),
@businesss_Field2 varchar(30),
@businesss_Field3 varchar(30),
@businesss_Field4 varchar(30),
@businesss_Field5 varchar(30),
@businesss_Field6 varchar(30),
@businesss_Field7 varchar(30),
@businesss_Field8 varchar(30),
@businesss_Field9 varchar(30),
@businesss_Field10 varchar(30),
@businesss_Field11 varchar(30),
@businesss_Field12 varchar(30),
@businesss_Field13 varchar(30),
@businesss_Field14 varchar(30)

set @business_counter=1

declare business_pointer Cursor for
Select
Cast(CHRON_ID as varchar(11)),
Cast(BUSINESS_NUMBER as varchar(11)),
Cast(YES_NO_STILL_OPERATING as varchar(11)),
Cast(CHOICE_TYPE as varchar(11)),
Cast(CHOICE_ACTIVITY as varchar(11)),
Cast(PERCENT_OWNED as varchar(11)),
Cast(ACTUAL_GROSS_RECEIPTS as varchar(11)),
Cast(ACTUAL_EXPENSES as varchar(11)),
Cast(ACTUAL_NET_INCOME as varchar(11)),
Cast(ESTIMATED_GROSS_RECEIPTS as varchar(11)),
Cast(ESTIMATED_EXPENSES as varchar(11)),
Cast(ESTIMATED_NET_INCOME as varchar(11)),
Cast(NET_INCOME_MANUAL_CALCULATION as varchar(11)),
Cast(CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER as varchar(11)),
Cast(CHOSEN_SCENARIO_AMOUNT as varchar(11))
from a_business
where chron_id=@CHRONID

open business_pointer

fetch next from business_pointer into
@CHRONID,
@BUSINESS_NUMBER,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS,
@ACTUAL_EXPENSES,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS,
@ESTIMATED_EXPENSES,
@ESTIMATED_NET_INCOME,
@NET_INCOME_MANUAL_CALCULATION,
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT

while @@fetch_status=0
begin
Set @businesss_Field1='Business_number'+@business_counter
Set @businesss_Field2='YES_NO_STILL_OPERATING'+@business_counter
Set @businesss_Field3='CHOICE_TYPE'+@business_counter
Set @businesss_Field4='CHOICE_ACTIVITY'+@business_counter
Set @businesss_Field5='PERCENT_OWNED'+@business_counter
Set @businesss_Field6='ACTUAL_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field7='ACTUAL_EXPENSES'+@business_counter
Set @businesss_Field8='@ACTUAL_NET_INCOME'+@business_counter
Set @businesss_Field9='@ESTIMATED_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field10='@ESTIMATED_EXPENSES'+@business_counter
Set @businesss_Field11='@ESTIMATED_NET_INCOME'+@business_counter
Set @businesss_Field12='@NET_INCOME_MANUAL_CALCULATION'+@business_counter
Set @businesss_Field13='@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER'+@business_counter
Set @businesss_Field14='@CHOSEN_SCENARIO_AMOUNT'+@business_counter

print @businesss_Field1
exec('UPDATE r_pdf_confidential_build_detail SET ' +@businesss_Field1+'=Cast(@business_number as varchar(11)), '+@businesss_Field2+'=Cast(@YES_NO_STILL_OPERATING as varchar(11)), '+@businesss_Field3+'=Cast(@CHOICE_TYPE as varchar(11)),'+@businesss_Field4+'=Cast(@CHOICE_ACTIVITY as varchar(11)), '+@businesss_Field5+'=Cast(@PERCENT_OWNED as varchar(11)),'+@businesss_Field6+'=Cast(@ACTUAL_GROSS_RECEIPTS as varchar(11)), '+@businesss_Field7+'=Cast(@ACTUAL_EXPENSES as varchar(11)), '+@businesss_Field8+'=Cast(@ACTUAL_NET_INCOME as varchar (11)),'+@businesss_Field9+'=Cast(@ESTIMATED_GROSS_RECEIPTS as varchar(11)), '+@businesss_Field10+'=Cast(@ESTIMATED_EXPENSES as varchar(11)), '+@businesss_Field11+'=Cast(@ESTIMATED_NET_INCOME as varchar(11)), '+@businesss_Field12+'=Cast(@NET_INCOME_MANUAL_CALCULATION as varchar(11)), '+@businesss_Field13+'=Cast(@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER as varchar(11)), '+@businesss_Field14+'=Cast(@CHOSEN_SCENARIO_AMOUNT as varchar(11))
where @business_number=@business_counter and @chronid=@chron_id')
set @business_counter=@business_counter+1
fetch next from business_pointer into
@CHRONID,
@BUSINESS_NUMBER,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS,
@ACTUAL_EXPENSES,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS,
@ESTIMATED_EXPENSES,
@ESTIMATED_NET_INCOME,
@NET_INCOME_MANUAL_CALCULATION,
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT
end
close business_pointer
deallocate business_pointer
GO

Logicalman

 

[zhavic]

One note at a start : MSSQL is not case sensitive.

The problem with this query is in EXEC statement, when executing string contained in query.

In this example:
DECLARE @cSomething char(1)
SET @cSomething = 'A'
EXECUTE( 'SELECT * FROM some_table WHERE some_comulng = @cSomething' )

variable '@cSomething' is defined in session that executes this statements, but EXECUTE statement creates new session to execute query string and in this new session the variable '@cSomething' is not defined.
Right query may look like this:

DECLARE @cSomething char(1)
SET @cSomething = 'A'
EXECUTE( 'SELECT * FROM some_table WHERE some_comulng = ''' + @cSomething + '''' )

OR like this, and this is what I suggest to using

DECLARE @cSomething char(1)
DECLARE @cSQL nvarchar(1000)
SET @cSomething = 'A'
SET @cSQL = 'SELECT * FROM some_table WHERE some_comulng = @cSomething'
EXECUTE sp_executesql
/* query string */
@cSQL,
/* list of parametres passed to the query */
N'@cSomething char(1)',
/* and parameters from list above */
@cSomething

----------------------------------------------------
So the EXEC statemenet from your query may look like this:

DECLARE @cSQL nvarchar(8000)
SET @cSQL =
'UPDATE r_pdf_confidential_build_detail SET ' +
@businesss_Field1+'=@business_number, ' +
@businesss_Field2+'=@YES_NO_STILL_OPERATING, ' +
@businesss_Field3+'=@CHOICE_TYPE,' +
@businesss_Field4+'=@CHOICE_ACTIVITY, ' +
@businesss_Field5+'=@PERCENT_OWNED,' +
@businesss_Field6+'=@ACTUAL_GROSS_RECEIPTS, ' +
@businesss_Field7+'=@ACTUAL_EXPENSES, ' +
@businesss_Field8+'=@ACTUAL_NET_INCOME,' +
@businesss_Field9+'=@ESTIMATED_GROSS_RECEIPTS, ' +
@businesss_Field10+'=@ESTIMATED_EXPENSES, ' +
@businesss_Field11+'=@ESTIMATED_NET_INCOME, ' +
@businesss_Field12+'=@NET_INCOME_MANUAL_CALCULATION, ' +
@businesss_Field13+'=@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER, ' +
@businesss_Field14+'=@CHOSEN_SCENARIO_AMOUNT' +
'where @business_number=@business_counter and @chronid=@chron_id'

EXECUTE sp_executesql
/* query string */
@cSQL,
/* list of parametres passed tothe query */
'@business_number varchar(11), ' +
'@YES_NO_STILL_OPERATING varchar(11), ' +
'@CHOICE_TYPE varchar(11),' +
'@CHOICE_ACTIVITY varchar(11), ' +
'@PERCENT_OWNED varchar(11),' +
'@ACTUAL_GROSS_RECEIPTS varchar(11), ' +
'@ACTUAL_EXPENSES varchar(11), ' +
'@ACTUAL_NET_INCOME varchar(11),' +
'@ESTIMATED_GROSS_RECEIPTS varchar(11), ' +
'@ESTIMATED_EXPENSES varchar(11), ' +
'@ESTIMATED_NET_INCOME varchar(11), ' +
'@NET_INCOME_MANUAL_CALCULATION varchar(11), ' +
'@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER varchar(11), ' +
'@CHOSEN_SCENARIO_AMOUNT varchar(11)' +
'@business_counter varchar(1)' +
'@chronid=CHRON_ID varchar(11)',
/* parameters from the above list */
@business_number,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY ,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS ,
@ACTUAL_EXPENSES ,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS ,
@ESTIMATED_EXPENSES ,
@ESTIMATED_NET_INCOME ,
@NET_INCOME_MANUAL_CALCULATION ,
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT,
@business_counter,
@chronid


-------------------------------------------------
But there is some questions about your typo:
1.)
Set @businesss_Field1='Business_number'+@business_counter
Set @businesss_Field2='YES_NO_STILL_OPERATING'+@business_counter
Set @businesss_Field3='CHOICE_TYPE'+@business_counter
Set @businesss_Field4='CHOICE_ACTIVITY'+@business_counter
Set @businesss_Field5='PERCENT_OWNED'+@business_counter
Set @businesss_Field6='ACTUAL_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field7='ACTUAL_EXPENSES'+@business_counter
Set @businesss_Field8='@ACTUAL_NET_INCOME'+@business_counter
Set @businesss_Field9='@ESTIMATED_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field10='@ESTIMATED_EXPENSES'+@business_counter
Set @businesss_Field11='@ESTIMATED_NET_INCOME'+@business_counter
Set @businesss_Field12='@NET_INCOME_MANUAL_CALCULATION'+@business_counter
Set @businesss_Field13='@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER'+@business_counter
Set @businesss_Field14='@CHOSEN_SCENARIO_AMOUNT'+@business_counter

may be

Set @businesss_Field1='Business_number'+@business_counter
Set @businesss_Field2='YES_NO_STILL_OPERATING'+@business_counter
Set @businesss_Field3='CHOICE_TYPE'+@business_counter
Set @businesss_Field4='CHOICE_ACTIVITY'+@business_counter
Set @businesss_Field5='PERCENT_OWNED'+@business_counter
Set @businesss_Field6='ACTUAL_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field7='ACTUAL_EXPENSES'+@business_counter
Set @businesss_Field8='ACTUAL_NET_INCOME'+@business_counter
Set @businesss_Field9='ESTIMATED_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field10='ESTIMATED_EXPENSES'+@business_counter
Set @businesss_Field11='ESTIMATED_NET_INCOME'+@business_counter
Set @businesss_Field12='NET_INCOME_MANUAL_CALCULATION'+@business_counter
Set @businesss_Field13='CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER'+@business_counter
Set @businesss_Field14='CHOSEN_SCENARIO_AMOUNT'+@business_counter

3.) Also as 'Logicalman' wrote, there is mistake in where clause in DECLARE CURSOR statement, you wrote
'where chron_id=@chron_id',
but as 'Logicalman' wrote, it may be
'where CHRON_ID=@chronid',

2.) In the where clause from query in EXEC statement there is
'where @business_number=@business_counter and @chronid=@chron_id'
I assume that '@business_number' and '@chron_id' from above are column names, so it may look like this:
'where BUSINESS_NUMBER=@business_counter and @chronid=CHRON_ID'


--------------------------------
After that, your query may look like this

Declare @CHRONID varchar(11),
@business_number varchar(11),
@YES_NO_STILL_OPERATING varchar(11),
@CHOICE_TYPE varchar(11),
@CHOICE_ACTIVITY varchar(11),
@PERCENT_OWNED varchar(11),
@ACTUAL_GROSS_RECEIPTS varchar(11),
@ACTUAL_EXPENSES varchar(11),
@ACTUAL_NET_INCOME varchar(11),
@ESTIMATED_GROSS_RECEIPTS varchar(11),
@ESTIMATED_EXPENSES varchar(11),
@ESTIMATED_NET_INCOME varchar(11),
@NET_INCOME_MANUAL_CALCULATION varchar(11),
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER varchar(11),
@CHOSEN_SCENARIO_AMOUNT varchar(11),
@business_counter varchar(1),
@businesss_Field1 varchar(30),
@businesss_Field2 varchar(30),
@businesss_Field3 varchar(30),
@businesss_Field4 varchar(30),
@businesss_Field5 varchar(30),
@businesss_Field6 varchar(30),
@businesss_Field7 varchar(30),
@businesss_Field8 varchar(30),
@businesss_Field9 varchar(30),
@businesss_Field10 varchar(30),
@businesss_Field11 varchar(30),
@businesss_Field12 varchar(30),
@businesss_Field13 varchar(30),
@businesss_Field14 varchar(30)

DECLARE @cSQL nvarchar(8000)

set @business_counter=1

declare business_pointer Cursor for
Select Cast(CHRON_ID as varchar(11)),
Cast(BUSINESS_NUMBER as varchar(11)),
Cast(YES_NO_STILL_OPERATING as varchar(11)),
Cast(CHOICE_TYPE as varchar(11)),
Cast(CHOICE_ACTIVITY as varchar(11)),
Cast(PERCENT_OWNED as varchar(11)),
Cast(ACTUAL_GROSS_RECEIPTS as varchar(11)),
Cast(ACTUAL_EXPENSES as varchar(11)),
Cast(ACTUAL_NET_INCOME as varchar(11)),
Cast(ESTIMATED_GROSS_RECEIPTS as varchar(11)),
Cast(ESTIMATED_EXPENSES as varchar(11)),
Cast(ESTIMATED_NET_INCOME as varchar(11)),
Cast(NET_INCOME_MANUAL_CALCULATION as varchar(11)),
Cast(CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER as varchar(11)),
Cast(CHOSEN_SCENARIO_AMOUNT as varchar(11))
from a_business
where chron_id=@chronid

open business_pointer

fetch next
from business_pointer
into @CHRONID,
@BUSINESS_NUMBER,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS,
@ACTUAL_EXPENSES,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS,
@ESTIMATED_EXPENSES,
@ESTIMATED_NET_INCOME,
@NET_INCOME_MANUAL_CALCULATION,
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT

while @@fetch_status=0
begin
Set @businesss_Field1='Business_number'+@business_counter
Set @businesss_Field2='YES_NO_STILL_OPERATING'+@business_counter
Set @businesss_Field3='CHOICE_TYPE'+@business_counter
Set @businesss_Field4='CHOICE_ACTIVITY'+@business_counter
Set @businesss_Field5='PERCENT_OWNED'+@business_counter
Set @businesss_Field6='ACTUAL_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field7='ACTUAL_EXPENSES'+@business_counter
Set @businesss_Field8='ACTUAL_NET_INCOME'+@business_counter
Set @businesss_Field9='ESTIMATED_GROSS_RECEIPTS'+@business_counter
Set @businesss_Field10='ESTIMATED_EXPENSES'+@business_counter
Set @businesss_Field11='ESTIMATED_NET_INCOME'+@business_counter
Set @businesss_Field12='NET_INCOME_MANUAL_CALCULATION'+@business_counter
Set @businesss_Field13='CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER'+@business_counter
Set @businesss_Field14='CHOSEN_SCENARIO_AMOUNT'+@business_counter

print @businesss_Field1

SET @cSQL = 'UPDATE r_pdf_confidential_build_detail SET ' +
@businesss_Field1+'=@business_number, ' +
@businesss_Field2+'=@YES_NO_STILL_OPERATING, ' +
@businesss_Field3+'=@CHOICE_TYPE,' +
@businesss_Field4+'=@CHOICE_ACTIVITY, ' +
@businesss_Field5+'=@PERCENT_OWNED,' +
@businesss_Field6+'=@ACTUAL_GROSS_RECEIPTS, ' +
@businesss_Field7+'=@ACTUAL_EXPENSES, ' +
@businesss_Field8+'=@ACTUAL_NET_INCOME,' +
@businesss_Field9+'=@ESTIMATED_GROSS_RECEIPTS, ' +
@businesss_Field10+'=@ESTIMATED_EXPENSES, ' +
@businesss_Field11+'=@ESTIMATED_NET_INCOME, ' +
@businesss_Field12+'=@NET_INCOME_MANUAL_CALCULATION, ' +
@businesss_Field13+'=@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER, ' +
@businesss_Field14+'=@CHOSEN_SCENARIO_AMOUNT' +
'where BUSINESS_NUMBER=@business_counter and @chronid=CHRON_ID'

EXECUTE sp_executesql
/* query string */
@cSQL,
/* list of parametres passed to the query */
'@business_number varchar(11),
@YES_NO_STILL_OPERATING varchar(11),
@CHOICE_TYPE varchar(11),
@CHOICE_ACTIVITY varchar(11),
@PERCENT_OWNED varchar(11),
@ACTUAL_GROSS_RECEIPTS varchar(11),
@ACTUAL_EXPENSES varchar(11),
@ACTUAL_NET_INCOME varchar(11),
@ESTIMATED_GROSS_RECEIPTS varchar(11),
@ESTIMATED_EXPENSES varchar(11),
@ESTIMATED_NET_INCOME varchar(11),
@NET_INCOME_MANUAL_CALCULATION varchar(11),
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER varchar(11),
@CHOSEN_SCENARIO_AMOUNT varchar(11),
@business_counter varchar(11),
@chronid varchar(11)',
/* parameters from the above list */
@business_number,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY ,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS ,
@ACTUAL_EXPENSES ,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS ,
@ESTIMATED_EXPENSES ,
@ESTIMATED_NET_INCOME ,
@NET_INCOME_MANUAL_CALCULATION ,
@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT,
@business_counter,
@chronid

set @business_counter=@business_counter+1

fetch next
from business_pointer
into @CHRONID,
@BUSINESS_NUMBER,
@YES_NO_STILL_OPERATING,
@CHOICE_TYPE,
@CHOICE_ACTIVITY,
@PERCENT_OWNED,
@ACTUAL_GROSS_RECEIPTS,
@ACTUAL_EXPENSES,
@ACTUAL_NET_INCOME,
@ESTIMATED_GROSS_RECEIPTS,
@ESTIMATED_EXPENSES,
@ESTIMATED_NET_INCOME,
@NET_INCOME_MANUAL_CALCULATION,

@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER,
@CHOSEN_SCENARIO_AMOUNT

end
close business_pointer
deallocate business_pointer

-----------------------------------------------------------------------------------

But some questions also above that
Why you are converting all columns from 'a_business' table to type VARCHAR(11) ?
Which of types are that columns ?
If you post there that types, we can write this query more effectively.

Also you can use something like macro for your columns in query string instead of multiple 'SET' commands:

/* macro is '%i' */
SET @cSQL = 'UPDATE r_pdf_confidential_build_detail SET ' +
'BUSINESS_NUMBER%i=@business_number, ' +
'YES_NO_STILL_OPERATING%i=@YES_NO_STILL_OPERATING, ' +
'CHOICE_TYPE%i=@CHOICE_TYPE,' +
'CHOICE_ACTIVITY%i=@CHOICE_ACTIVITY, ' +
'PERCENT_OWNED%i=@PERCENT_OWNED,' +
'ACTUAL_GROSS_RECEIPTS%i=@ACTUAL_GROSS_RECEIPTS, ' +
'ACTUAL_EXPENSES%i=@ACTUAL_EXPENSES, ' +
'ACTUAL_NET_INCOME%i=@ACTUAL_NET_INCOME,' +
'ESTIMATED_GROSS_RECEIPTS%i=@ESTIMATED_GROSS_RECEIPTS, ' +
'ESTIMATED_EXPENSES%i=@ESTIMATED_EXPENSES, ' +
'ESTIMATED_NET_INCOME%i=@ESTIMATED_NET_INCOME, ' +
'NET_INCOME_MANUAL_CALCULATION%i=@NET_INCOME_MANUAL_CALCULATION, ' +
'CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER%i=@CHOICE_NET_INCOME_CHOSEN_SCENARIO_NUMBER, ' +
'CHOSEN_SCENARIO_AMOUNT%i=@CHOSEN_SCENARIO_AMOUNT' +
'where BUSINESS_NUMBER=@business_counter and @chronid=CHRON_ID'

/* replace macro ( '%i' ) with real value */
SET @cSQL = REPLACE( @cSQL, '%i', RTRIM( LTRIM( @business_counter ) ) )


Let's try something from this and post back you get or your questions to that


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[LogicalmanUS]

Case sensitivity is determined in the collation selected.
If a Case-Sensitive collation is selected, then an error will be reported from an sp where a table is created as #Customers, but then referenced using #customers.
Unfortunately this translates with passwords also.

When I checked the sp you supplied, and changed the variables to be exactly as they were declared, the procedure worked correctly.

This, apparently, is an un-declared bug (sorry - feature) in SQL server.

Reference article:

http://www.securityfocus.com/archive/1/281548/2002-07-10/2002-07-16/0

Logicalman