about Tsql of Cursor

declare @.SourceName varchar(40)
select @.SourceName=@.SourcePre+'SizeDefine'
Declare MyCursor Cursor for
select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
@.SourceName
open MyCursor
--
@.SourceName is a table name, and depend on user input
the error is'Must declare the variable '@.SourceName'.'
i guess the varible @.SourceName need something to make
system think it is a table ,what shall i do?thanks a lotdo...
declare @.SourceName varchar(40)
select @.SourceName=@.SourcePre+'SizeDefine'
exec('Declare MyCursor Cursor for
select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
@.SourceName )
open MyCursor
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> declare @.SourceName varchar(40)
> select @.SourceName=@.SourcePre+'SizeDefine'
> Declare MyCursor Cursor for
> select
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
> @.SourceName
> open MyCursor
> --
> @.SourceName is a table name, and depend on user input
> the error is'Must declare the variable '@.SourceName'.'
> i guess the varible @.SourceName need something to make
> system think it is a table ,what shall i do?thanks a lot|||Why do you want to reference table names dynamically in this way? You appear
to have multiple tables of the same structure which you want to reference
with a single SELECT statement. This usually indicates a flawed design that
can be fixed by consolidating your tables into one. For example your tables
might look like this:
CREATE TABLE
Accounts_2001 (accountno INTEGER PRIMARY KEY, amount NUMERIC(10,2)...)
CREATE TABLE
Accounts_2002 (accountno INTEGER PRIMARY KEY, amount NUMERIC(10,2)...)
In which case you should combine them into a single table and add an extra
column to the primary key:
CREATE TABLE Accounts (yearno INTEGER CHECK (yearno BETWEEN 2000 AND 2100),
accountno INTEGER, amount NUMERIC(10,2)... , PRIMARY KEY (yearno,
accountno))
This is a much more effective design and your "@.source" parameter then just
becomes part of the WHERE clause of a SELECT statement.
Finally, why are you using a cursor? 99.99% of the time cursors are
unnecessary. You should always try to avoid cursors because of their poor
performance and their resource overhead. If you need help finding a
non-cursor solution then post your DDL (CREATE TABLE statements including
keys and constraints), include some sample data (INSERT statements) and also
post your cursor code.
--
David Portas
--
Please reply only to the newsgroup
--|||thanks a lot
and another question:)
declare @.S_nSizeID
exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName)
i want the value of @.S_nSizeID but the system tell
me @.S_nSizeID don't declare,how to change this script
thanks
>--Original Message--
>do...
>declare @.SourceName varchar(40)
>select @.SourceName=@.SourcePre+'SizeDefine'
>exec('Declare MyCursor Cursor for
>select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
G
>ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
>@.SourceName )
>open MyCursor
>
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> declare @.SourceName varchar(40)
>> select @.SourceName=@.SourcePre+'SizeDefine'
>> Declare MyCursor Cursor for
>> select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
>> @.SourceName
>> open MyCursor
>> --
>> @.SourceName is a table name, and depend on user input
>> the error is'Must declare the variable '@.SourceName'.'
>> i guess the varible @.SourceName need something to make
>> system think it is a table ,what shall i do?thanks a lot
>
>.
>|||thanks a lot
and another question:)
declare @.S_nSizeID
exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName)
i want the value of @.S_nSizeID but the system tell
me @.S_nSizeID don't declare,how to change this script
thanks
>--Original Message--
>do...
>declare @.SourceName varchar(40)
>select @.SourceName=@.SourcePre+'SizeDefine'
>exec('Declare MyCursor Cursor for
>select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
G
>ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
>@.SourceName )
>open MyCursor
>
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> declare @.SourceName varchar(40)
>> select @.SourceName=@.SourcePre+'SizeDefine'
>> Declare MyCursor Cursor for
>> select
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
>> @.SourceName
>> open MyCursor
>> --
>> @.SourceName is a table name, and depend on user input
>> the error is'Must declare the variable '@.SourceName'.'
>> i guess the varible @.SourceName need something to make
>> system think it is a table ,what shall i do?thanks a lot
>
>.
>|||thanks
but my store procedure is to Export and change data
between database base,because the database name is
depend on user input,so the table name is different
and need split one table data to several tables
such as
--
Declare @.CTD_strID char(8)
Declare @.CTD_strDescription varchar(255)
Declare @.CTD_nSortNum smallint
Declare @.CTD_bIsValid char(1)
Declare @.CTD_nGridID int
Declare @.CT_nCategoryID int
exec('Declare MyCursor Cursor for '+
'select
CTD_strID,CTD_strDescription,CTD_nSortNum,CTD_bIsValid,CTD_
nGridID from '+@.SourceName)
open MyCursor
FETCH NEXT FROM MyCursor
INTO @.CTD_strID,
@.CTD_strDescription ,@.CTD_nSortNum,@.CTD_bIsValid,@.CTD_nGrid
ID
WHILE @.@.FETCH_STATUS = 0
begin
select @.DestName=@.DestPre+'CategoryTree'
exec('select '+@.CT_nCategoryID+'=isnull(max
(CT_nCategoryID),0)+1 from '+@.DestName)
exec('insert
into '+@.DestName+'(CT_nCategoryID,CT_strCode,CT_strDescript
ion,CT_nSortNum,CT_nParentID,CT_cIfValid,CT_dtModifyTime)'+
'values
('+@.CT_nCategoryID+','+@.CTD_strID+','+@.CTD_strDescription+'
,'+@.CTD_nSortNum+','+@.CT_nCategoryID_Parent+','+@.CTD_bIsVal
id+',getdate())' )
select @.DestName=@.DestPre+'CategoryInAdType'
exec('insert
into '+@.DestName+'(CIT_nCategoryTreeID,CIT_nAdTypeID)'+
'values('+@.CT_nCategoryID+','+@.CTD_nGridID+')' )
end
close MyCursor
deallocate MyCursor
--
do you have any ideas about it don't use cursor?
thanks a lot
>--Original Message--
>Why do you want to reference table names dynamically in
this way? You appear
>to have multiple tables of the same structure which you
want to reference
>with a single SELECT statement. This usually indicates a
flawed design that
>can be fixed by consolidating your tables into one. For
example your tables
>might look like this:
>CREATE TABLE
> Accounts_2001 (accountno INTEGER PRIMARY KEY, amount
NUMERIC(10,2)...)
>CREATE TABLE
> Accounts_2002 (accountno INTEGER PRIMARY KEY, amount
NUMERIC(10,2)...)
>In which case you should combine them into a single table
and add an extra
>column to the primary key:
>CREATE TABLE Accounts (yearno INTEGER CHECK (yearno
BETWEEN 2000 AND 2100),
>accountno INTEGER, amount NUMERIC(10,2)... , PRIMARY KEY
(yearno,
>accountno))
>This is a much more effective design and your "@.source"
parameter then just
>becomes part of the WHERE clause of a SELECT statement.
>Finally, why are you using a cursor? 99.99% of the time
cursors are
>unnecessary. You should always try to avoid cursors
because of their poor
>performance and their resource overhead. If you need help
finding a
>non-cursor solution then post your DDL (CREATE TABLE
statements including
>keys and constraints), include some sample data (INSERT
statements) and also
>post your cursor code.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>|||You will have to use sp_executesql to obtain the output.
e.g.
declare @.S_nSizeID int, @.sql nvarchar(1000)
set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1) from '+@.DestName
exec sp_executesql @.sql, N'@.S_nSizeID int output', @.S_nSizeID
select @.S_nSizeID
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:081501c3b009$4c086a40$a301280a@.phx.gbl...
> thanks a lot
> and another question:)
> declare @.S_nSizeID
> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> from '+@.DestName)
> i want the value of @.S_nSizeID but the system tell
> me @.S_nSizeID don't declare,how to change this script
> thanks
>
>
> >--Original Message--
> >do...
> >
> >declare @.SourceName varchar(40)
> >select @.SourceName=@.SourcePre+'SizeDefine'
> >
> >exec('Declare MyCursor Cursor for
> >select
> >SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
> G
> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from '+
> >@.SourceName )
> >
> >open MyCursor
> >
> >
> >
> >--
> >-oj
> >RAC v2.2 & QALite!
> >http://www.rac4sql.net
> >
> >
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> >> declare @.SourceName varchar(40)
> >> select @.SourceName=@.SourcePre+'SizeDefine'
> >>
> >> Declare MyCursor Cursor for
> >> select
> >>
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange from
> >> @.SourceName
> >>
> >> open MyCursor
> >>
> >> --
> >> @.SourceName is a table name, and depend on user input
> >> the error is'Must declare the variable '@.SourceName'.'
> >> i guess the varible @.SourceName need something to make
> >> system think it is a table ,what shall i do?thanks a lot
> >
> >
> >.
> >|||with many thanks,oj
>--Original Message--
>You will have to use sp_executesql to obtain the output.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID
>select @.S_nSizeID
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> thanks a lot
>> and another question:)
>> declare @.S_nSizeID
>> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> from '+@.DestName)
>> i want the value of @.S_nSizeID but the system tell
>> me @.S_nSizeID don't declare,how to change this script
>> thanks
>>
>>
>> >--Original Message--
>> >do...
>> >
>> >declare @.SourceName varchar(40)
>> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >
>> >exec('Declare MyCursor Cursor for
>> >select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> G
>> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from '+
>> >@.SourceName )
>> >
>> >open MyCursor
>> >
>> >
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> declare @.SourceName varchar(40)
>> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >>
>> >> Declare MyCursor Cursor for
>> >> select
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from
>> >> @.SourceName
>> >>
>> >> open MyCursor
>> >>
>> >> --
>> >> @.SourceName is a table name, and depend on user input
>> >> the error is'Must declare the
variable '@.SourceName'.'
>> >> i guess the varible @.SourceName need something to
make
>> >> system think it is a table ,what shall i do?thanks a
lot
>> >
>> >
>> >.
>> >
>
>.
>|||hello oj:
i test the script you write ,but @.S_nSizeID
always return NULL,it seems the para output don't work,
ao you have any ideas?thank you
>--Original Message--
>You will have to use sp_executesql to obtain the output.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID
>select @.S_nSizeID
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> thanks a lot
>> and another question:)
>> declare @.S_nSizeID
>> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> from '+@.DestName)
>> i want the value of @.S_nSizeID but the system tell
>> me @.S_nSizeID don't declare,how to change this script
>> thanks
>>
>>
>> >--Original Message--
>> >do...
>> >
>> >declare @.SourceName varchar(40)
>> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >
>> >exec('Declare MyCursor Cursor for
>> >select
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> G
>> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from '+
>> >@.SourceName )
>> >
>> >open MyCursor
>> >
>> >
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> declare @.SourceName varchar(40)
>> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >>
>> >> Declare MyCursor Cursor for
>> >> select
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
from
>> >> @.SourceName
>> >>
>> >> open MyCursor
>> >>
>> >> --
>> >> @.SourceName is a table name, and depend on user input
>> >> the error is'Must declare the
variable '@.SourceName'.'
>> >> i guess the varible @.SourceName need something to
make
>> >> system think it is a table ,what shall i do?thanks a
lot
>> >
>> >
>> >.
>> >
>
>.
>|||Sorry for the late response...Anyway, I've left a key in that last post.
Please try this.
e.g.
declare @.S_nSizeID int, @.sql nvarchar(1000)
set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1) from '+@.DestName
exec sp_executesql @.sql, N'@.S_nSizeID int output', @.S_nSizeID OUTPUT
select @.S_nSizeID
HTH.
--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"frank" <anonymous@.discussions.microsoft.com> wrote in message
news:006f01c3b0b2$d168cf00$a001280a@.phx.gbl...
> hello oj:
> i test the script you write ,but @.S_nSizeID
> always return NULL,it seems the para output don't work,
> ao you have any ideas?thank you
> >--Original Message--
> >You will have to use sp_executesql to obtain the output.
> >
> >e.g.
> >declare @.S_nSizeID int, @.sql nvarchar(1000)
> >
> >set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> from '+@.DestName
> >exec sp_executesql @.sql, N'@.S_nSizeID int output',
> @.S_nSizeID
> >
> >select @.S_nSizeID
> >
> >--
> >-oj
> >RAC v2.2 & QALite!
> >http://www.rac4sql.net
> >
> >
> >
> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:081501c3b009$4c086a40$a301280a@.phx.gbl...
> >> thanks a lot
> >> and another question:)
> >>
> >> declare @.S_nSizeID
> >> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
> >> from '+@.DestName)
> >>
> >> i want the value of @.S_nSizeID but the system tell
> >> me @.S_nSizeID don't declare,how to change this script
> >> thanks
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >do...
> >> >
> >> >declare @.SourceName varchar(40)
> >> >select @.SourceName=@.SourcePre+'SizeDefine'
> >> >
> >> >exec('Declare MyCursor Cursor for
> >> >select
> >>
> >SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
> >> G
> >> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
> from '+
> >> >@.SourceName )
> >> >
> >> >open MyCursor
> >> >
> >> >
> >> >
> >> >--
> >> >-oj
> >> >RAC v2.2 & QALite!
> >> >http://www.rac4sql.net
> >> >
> >> >
> >> >
> >> >"frank" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
> >> >> declare @.SourceName varchar(40)
> >> >> select @.SourceName=@.SourcePre+'SizeDefine'
> >> >>
> >> >> Declare MyCursor Cursor for
> >> >> select
> >> >>
> >>
> SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
> >> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
> from
> >> >> @.SourceName
> >> >>
> >> >> open MyCursor
> >> >>
> >> >> --
> >> >> @.SourceName is a table name, and depend on user input
> >> >> the error is'Must declare the
> variable '@.SourceName'.'
> >> >> i guess the varible @.SourceName need something to
> make
> >> >> system think it is a table ,what shall i do?thanks a
> lot
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||thank you very much!
>--Original Message--
>Sorry for the late response...Anyway, I've left a key in
that last post.
>Please try this.
>e.g.
>declare @.S_nSizeID int, @.sql nvarchar(1000)
>set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
from '+@.DestName
>exec sp_executesql @.sql, N'@.S_nSizeID int output',
@.S_nSizeID OUTPUT
>select @.S_nSizeID
>HTH.
>--
>-oj
>RAC v2.2 & QALite!
>http://www.rac4sql.net
>
>"frank" <anonymous@.discussions.microsoft.com> wrote in
message
>news:006f01c3b0b2$d168cf00$a001280a@.phx.gbl...
>> hello oj:
>> i test the script you write ,but @.S_nSizeID
>> always return NULL,it seems the para output don't work,
>> ao you have any ideas?thank you
>> >--Original Message--
>> >You will have to use sp_executesql to obtain the
output.
>> >
>> >e.g.
>> >declare @.S_nSizeID int, @.sql nvarchar(1000)
>> >
>> >set @.sql ='select @.S_nSizeID=(isnull(max(S_nSizeID),0)
+1)
>> from '+@.DestName
>> >exec sp_executesql @.sql, N'@.S_nSizeID int output',
>> @.S_nSizeID
>> >
>> >select @.S_nSizeID
>> >
>> >--
>> >-oj
>> >RAC v2.2 & QALite!
>> >http://www.rac4sql.net
>> >
>> >
>> >
>> >"frank" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:081501c3b009$4c086a40$a301280a@.phx.gbl...
>> >> thanks a lot
>> >> and another question:)
>> >>
>> >> declare @.S_nSizeID
>> >> exec('select @.S_nSizeID=(isnull(max(S_nSizeID),0)+1)
>> >> from '+@.DestName)
>> >>
>> >> i want the value of @.S_nSizeID but the system tell
>> >> me @.S_nSizeID don't declare,how to change this script
>> >> thanks
>> >>
>> >>
>> >>
>> >>
>> >> >--Original Message--
>> >> >do...
>> >> >
>> >> >declare @.SourceName varchar(40)
>> >> >select @.SourceName=@.SourcePre+'SizeDefine'
>> >> >
>> >> >exec('Declare MyCursor Cursor for
>> >> >select
>> >>
>SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_n
>> >> G
>> >> >ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
>> from '+
>> >> >@.SourceName )
>> >> >
>> >> >open MyCursor
>> >> >
>> >> >
>> >> >
>> >> >--
>> >> >-oj
>> >> >RAC v2.2 & QALite!
>> >> >http://www.rac4sql.net
>> >> >
>> >> >
>> >> >
>> >> >"frank" <anonymous@.discussions.microsoft.com> wrote
in
>> >> message
>> >> >news:085f01c3af38$b02574e0$a401280a@.phx.gbl...
>> >> >> declare @.SourceName varchar(40)
>> >> >> select @.SourceName=@.SourcePre+'SizeDefine'
>> >> >>
>> >> >> Declare MyCursor Cursor for
>> >> >> select
>> >> >>
>> >>
SD_strSizeCode,SD_fWidth,SD_fHeight,SD_strDescription,SD_nG
>> >> >> ridID,SD_nSortNum,SD_bWidthChange,SD_bHeightChange
>> from
>> >> >> @.SourceName
>> >> >>
>> >> >> open MyCursor
>> >> >>
>> >> >> --
>> >> >> @.SourceName is a table name, and depend on user
input
>> >> >> the error is'Must declare the
>> variable '@.SourceName'.'
>> >> >> i guess the varible @.SourceName need something to
>> make
>> >> >> system think it is a table ,what shall i do?
thanks a
>> lot
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>