Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
|||Do not think there are arrays you can use fixed length , delimited or
XML
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
Showing posts with label declare. Show all posts
Showing posts with label declare. Show all posts
Sunday, March 11, 2012
Accept Multiple Values
Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
--
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
--
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
Accept Multiple Values
Hi ,
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
Is it possible to declare a variable to accept mutiple values at a time ?
for example :
Declare @.Country char(100)
Set @.country = 'TH','MY'"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:DF03833A-4A65-4969-A661-D98DCB859489@.microsoft.com...
> Hi ,
> Is it possible to declare a variable to accept mutiple values at a time
> ?
> for example :
> Declare @.Country char(100)
> Set @.country = 'TH','MY'
>
No arrays in SQL Server. What are you trying to do? Maybe this will help:
http://www.sommarskog.se/arrays-in-sql.html
David Portas
SQL Server MVP
--|||Do not think there are arrays you can use fixed length , delimited or
XML
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/
Tuesday, March 6, 2012
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
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>
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
>> >> >
>> >> >
>> >> >.
>> >> >
>> >
>> >
>> >.
>> >
>
>.
>
About TRAN, CURSOR and @@ERROR
Please take a look at my code below:
CREATE PROCEDURE ...
....
@.userId int,
....
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
Henri
Why use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>
|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...[vbcol=seagreen]
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
...[vbcol=seagreen]
@.confKey[vbcol=seagreen]
an
>
|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>
|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
David Portas
SQL Server MVP
|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
>
>
|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...[vbcol=seagreen]
when[vbcol=seagreen]
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
that my[vbcol=seagreen]
specified[vbcol=seagreen]
when[vbcol=seagreen]
if[vbcol=seagreen]
updated.[vbcol=seagreen]
no[vbcol=seagreen]
this
>
|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> when
> that my
> specified
> when
> if
> updated.
> no
> this
>
>
CREATE PROCEDURE ...
....
@.userId int,
....
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
Henri
Why use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>
|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...[vbcol=seagreen]
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
...[vbcol=seagreen]
@.confKey[vbcol=seagreen]
an
>
|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>
|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
David Portas
SQL Server MVP
|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
>
>
|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>
|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a crit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...[vbcol=seagreen]
when[vbcol=seagreen]
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
that my[vbcol=seagreen]
specified[vbcol=seagreen]
when[vbcol=seagreen]
if[vbcol=seagreen]
updated.[vbcol=seagreen]
no[vbcol=seagreen]
this
>
|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> when
> that my
> specified
> when
> if
> updated.
> no
> this
>
>
About TRAN, CURSOR and @@ERROR
Please take a look at my code below:
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--
Subscribe to:
Posts (Atom)