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.
