SQL About AMO: int

Showing posts with label int. Show all posts

Tuesday, March 6, 2012

about Transaction's problem

There is a table named users in my database.
It has two colume:
userName varchar(20)
age int
now I execute the follow transaction:
begin tran
insert users (userName,age) values ('mark',25)
insert users (userName,age) values ('jack',2577777777777767777)
commit tran
obviously,the second setence has an overflow error,
the question is:has the first record been inserted into the users table?
why?"ÎºÊöÀÚ" <weisl@.netstars.com.cn> wrote in message
news:%23T$6pCrPGHA.140@.TK2MSFTNGP12.phx.gbl...
> There is a table named users in my database.
> It has two colume:
> userName varchar(20)
> age int
> now I execute the follow transaction:
> begin tran
> insert users (userName,age) values ('mark',25)
> insert users (userName,age) values ('jack',2577777777777767777)
> commit tran
> obviously,the second setence has an overflow error,
> the question is:has the first record been inserted into the users table?
> why?
>
Because you told it to. You need error checking (or try/catch in SQL 2005)
to prevent execution from moving from the line that generates the error to
the line that commits the transaction.
David

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
>
>

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
--

Saturday, February 11, 2012

About EXEC dynamic query ?

Hello SQL Scripters,
I have the following MS/SQL server 2000 Script:
*******************
use NorthWind
GO
declare @.RecordCount int
,@.SelectString varchar(80)
,@.QueryString varchar(80)
,@.sQuote varchar(1)
set @.sQuote=''''
set @.QueryString='LastName like '+@.sQuote+'%'+'Kos'+'%'+@.sQuote
set @.SelectString='select count(*) from dbo.Employees where
'+@.QueryString
set @.RecordCount=0
exec(@.SelectString)
GO
*******************
How can I extract the result to the local variable @.RecordCount ?
In my case, I have to use the EXEC method to perform dynamic query.
TIA
Thomas
I got it. I use @.@.ROWCOUNT
Thanks
Thomas
|||No actually that is not the correct way. First off there is no need to use
dynamic sql at all here as the SARG can be a variable.
DECLARE @.x VARCHAR(50), @.y INT
SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
select count(*) from dbo.Employees where
LastName like @.x
If you want the count in a variable you can do this:
select @.y = count(*) from dbo.Employees where
LastName like @.x
And if you did have the need to use dynamic sql and wanted to return a value
you should use sp_executesql instead. See here for more details:
http://www.support.microsoft.com/?id=262499 Using OutPut Params &
sp_executeSql
http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>I got it. I use @.@.ROWCOUNT
> Thanks
> Thomas
>
|||Andrew,
Thanks for your response. The problem with my requirement is that I have to
construct the dynamic SQL string. This is a "Procedure" that allows the user
to submit fields s/he wishes to query against a selected Table and selected
list of fields within a selected table at runtime.
Therefore, your instruction "select count(*) from dbo.Employees where
LastName like @.x" will not work for me because the field "LastName" is also
dynamic (unknown at design time).
Here is my Procedure in real life: (@.QryString can be as "LastName like
'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName can
be "GolfTournament"; @.RV=Return Value that holds the number of records; and
finally, the returned dataset (not shown here))
****************************
Create Procedure tQuery
@.QryString varchar(2048)
, @.Tablename varchar(60)
, @.RV int out as
Declare @.s varchar(2048)
set @.s='select * from '+@.TableName+' Where '+@.QryString
EXEC(@.s)
set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
records returned by the @.QryString
Return @.RV
Go
*****************************
As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s). My
goal is to count 1st to make sure there is data for a submitted QryString,
but I just cannot simply say:
set @.RV=(select count(*) from @.Tablename where @.QryString
The above instreuction just does not compile.
Thomas
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
> No actually that is not the correct way. First off there is no need to
> use dynamic sql at all here as the SARG can be a variable.
>
> DECLARE @.x VARCHAR(50), @.y INT
> SET @.x = @.sQuote+'%'+'Kos'+'%'+@.sQuote
> select count(*) from dbo.Employees where
> LastName like @.x
>
> If you want the count in a variable you can do this:
>
> select @.y = count(*) from dbo.Employees where
> LastName like @.x
>
> And if you did have the need to use dynamic sql and wanted to return a
> value you should use sp_executesql instead. See here for more details:
>
> http://www.support.microsoft.com/?id=262499 Using OutPut Params &
> sp_executeSql
> http://www.sommarskog.se/dynamic_sql.html Dynamic SQL
>
> --
> Andrew J. Kelly SQL MVP
>
> "Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
> news:OrH$yzr3EHA.1188@.tk2msftngp13.phx.gbl...
>
|||If you simply want to find out if there are any rows that match the query
you should use EXISTS instead of count(*) as it can be much more efficient.
But in any case the links I sent should be helpful in working with dynamic
sql.
Andrew J. Kelly SQL MVP
"Thomas McFarlane" <TM@.HotMeal.Com> wrote in message
news:efgZqjs3EHA.4004@.tk2msftngp13.phx.gbl...
> Andrew,
> Thanks for your response. The problem with my requirement is that I have
> to construct the dynamic SQL string. This is a "Procedure" that allows the
> user to submit fields s/he wishes to query against a selected Table and
> selected list of fields within a selected table at runtime.
> Therefore, your instruction "select count(*) from dbo.Employees where
> LastName like @.x" will not work for me because the field "LastName" is
> also dynamic (unknown at design time).
> Here is my Procedure in real life: (@.QryString can be as "LastName like
> 'Woo%' and Occupation='Golf' order by LastName,WinningScore; @.TableName
> can be "GolfTournament"; @.RV=Return Value that holds the number of
> records; and finally, the returned dataset (not shown here))
> ****************************
> Create Procedure tQuery
> @.QryString varchar(2048)
> , @.Tablename varchar(60)
> , @.RV int out as
> Declare @.s varchar(2048)
> set @.s='select * from '+@.TableName+' Where '+@.QryString
> EXEC(@.s)
> set @.RV=@.@.ROWCOUNT --This always gives me the accurate number of
> records returned by the @.QryString
> Return @.RV
> Go
> *****************************
> As a result, I had to use the @.@.ROWCOUNT immediately after the EXEC(@.s).
> My goal is to count 1st to make sure there is data for a submitted
> QryString, but I just cannot simply say:
> set @.RV=(select count(*) from @.Tablename where @.QryString
> The above instreuction just does not compile.
> Thomas
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23umX%23Ks3EHA.1976@.TK2MSFTNGP09.phx.gbl...
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23ZRd5tt3EHA.2804@.TK2MSFTNGP15.phx.gbl...
> If you simply want to find out if there are any rows that match the query
> you should use EXISTS instead of count(*) as it can be much more
> efficient. But in any case the links I sent should be helpful in working
> with dynamic sql.
>
Thanks Andrew. I need the RecordCount for calculation purposes. But the
EXISTS function is definitely good. I am exploring more about the links you
gave. Very good stuffs.
Take care!
Thomas

Tuesday, March 6, 2012

about Transaction's problem

About TRAN, CURSOR and @@ERROR

About TRAN, CURSOR and @@ERROR

Saturday, February 11, 2012

About EXEC dynamic query ?

About EXEC dynamic query ?

SQL About AMO

Blog Archive

About Me