Possible to specifying a list as a parameter to a stored procedure ?

I have been converting a VB 6 applications database queries into SQL Server 2000 stored procedures and have come up against a problem where lists are used in search conditions...For example a list of accounts are selected based on their account currency ID being equal to 1, 5, or 7. In the VB 6 query the string looks like...

SELECT tblAccount.txtName FROM tblAccount WHERE (tblAccount.intCurrencyId IN(1, 5, 7))

The list could contain a single value or upto 20 values. Is it possible to pass the currency list (i.e "1, 5, 7, ...") as a parameter to the stored procedure?

Any help much appreciated!The answer is, "maybe".

It depends on your needs for performance. Please take a look at this discussion for more details.

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=15403

Essentially, if you pass the list as a comma delimited string, then you will either need to parse it inside the query or use it in a dynamic SQL query within the SP. Your other choice is to take all 20 objects as single parameters to your SP. Your IN statement would then be a large set of OR statements for each of the 20 items.

I hope this helps,

CC|||I have to write a lot of stored procedures for reports. I always declare my parameters like

level1 varchar(255)

I then look at the incoming value. I use charindex to find ';' or ',' If I find either I know I have to use "in" in the where clause and format the values correctly.

IF CHARINDEX(';',@.LEVEL1)>0
BEGIN
SET @.LEVEL1=REPLACE('('+''''+REPLACE(@.LEVEL1,';',''''+ ','+'''')+''''+')',' ','')
END

If it is prompt is equal to '%' for all I make my where clause a like, if it is a single value I use equal. The trick to making this so flexible is to use dynamic sql. If you don't know what the parameter will be before hand it seems to be the best way.

' AND ISNULL(T1.DIVISION,'+''''+'NONE'+''''+')' +
case when CHARINDEX(',',@.LEVEL1)>0 then 'in '+@.LEVEL1
else
CASE @.LEVEL1 when '%' THEN ' LIKE '+''''+@.LEVEL1+''''+'+'+''''+'%'+''''
ELSE '='+''''+@.LEVEL1+'''' END
END

Sorry for the formating. It looks better in the actual file|||Oh and I just realized something. If the incoming value is '%' then do not add a condition for it in the dynamic where clause. It makes zero sense to add anything to a where clause if you don't need to.

Possible to overuse WITH (NOLOCK)?

I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chris
On Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:

>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.

>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP
|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software
|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris
|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:

>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)

>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP
|||"Hugo Kornelis" wrote:

> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.

> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris

Possible to overuse WITH (NOLOCK)?

I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed o
r
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I ca
n
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:

>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.

>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. A
s
far as reading uncomitted data when using nolock, that's not a problem as th
e
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes th
e
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, s
o
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:

>Thanks David & Hugo... pretty much confirmed what I had already suspected.
As
>far as reading uncomitted data when using nolock, that's not a problem as t
he
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)

>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll jus
t
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:

> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.

> (snip)
>
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement
,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris

Possible to overuse WITH (NOLOCK)?

I'm working with a process that is initially invoked from VB, but runs
through a set of stored procedures. The first four levels of the calls are
simply gathering data that the other levels below will need and aggregating
some of it. This data is used to determine if further processing is needed or
not, and if so, calls the next level. W/o going into too much detail, since
the upper levels are simply data gathering, I've been rather liberal in my
application of WITH (NOLOCK) on virtually every table that is used in the
look up.
Here's the reason why, initially the process took two hours to run and it
didn't fully complete. Since my users are not in a Snickers commercial, I can
hardly expect them to wait that long for this process. So I need to make it
as fast as possible. I've gone through and changed all my CURSORS to select
loops (mucho improvement) and also dumped #tempTables in favor of table
variables (more improvement)... and then I added WITH (NOLOCK) on every
normal (non temp or derrived - ie inner selects) table in all queries... I
notice more improvement again. I also rearranged a few queries where some
calculations were done breaking out the data into two parts and changed a
LEFT JOIN to an INNER JOIN....
IS the ANY thing else I can do to squeeze some performance out of this
monster? I haven't run it through profiler yet, but that's the next obvious
choice.
The other half of the query is: is it possible to go too far with WITH
(NOLOCK)? Or is what I've done reasonable?
=chrisOn Tue, 28 Feb 2006 13:16:26 -0800, CAnderson wrote:
>The other half of the query is: is it possible to go too far with WITH
>(NOLOCK)? Or is what I've done reasonable?
Hi Chris,
I'll start here.
If performance gain is your sole target, you can use this hint freely.
But if you want correct reports, beware. As another poster in this
groups once said it: WITH (NOLOCK) can give you incorrect results at a
blinding speed.
SQL Server will normally lock data that has been changed but not yet
committed. Other queries have to wait for this lock to be released
before they can access the data. With WITH (NOLOCK), you ignore the
lock, which means that you'll read the uncommitted data. This saves lots
of time if there are locks, and it even saves some time if there are no
locks since you bypass the overhead of checking for locks.
But the downside is that you can read uncommitted data. Suppose that I
update a column to one billion dollars negative. Some sanity check in a
trigger will probably catch this and rollback my transaction - but if
your report runs in the periode between my submitting the update and the
trigger rolling it back, your report will be off by a billion dollars.
Another example - suppose a transaction is debiting your account and
crediting mine. Your report runs before my account is credited, but
after yours is debited. Now, the totals on the left-hand side of your
report won't match those on the right-hand side and all bookkeepers,
accountants and controllers in your company will go crazy.
>I've gone through and changed all my CURSORS to select
>loops (mucho improvement) and also dumped #tempTables in favor of table
>variables (more improvement)...
(snip)
>IS the ANY thing else I can do to squeeze some performance out of this
>monster?
Revisit your code. Try to get rid of all cursors, all select loops, all
temp tables and all table variables. SQL Server is optimized for
declarative, set-based processing. All procedural, row-based code (both
cursor and select loop; both temp table and table variable) will almost
always be slower than one single or a short batch of set-based queries.
Check if all your queries use indexes. Add indexes where necessary.
Remove unused indexes. Pay special attention to your choice of clustered
index.
If you need more specific help than this, you'll need to give more
specific information. Check out www.aspfaq.com/5006.
--
Hugo Kornelis, SQL Server MVP|||CAnderson [MVP] wrote:
> I'm working with a process that is initially invoked from VB, but runs
> through a set of stored procedures. The first four levels of the
> calls are simply gathering data that the other levels below will need
> and aggregating some of it. This data is used to determine if further
> processing is needed or not, and if so, calls the next level. W/o
> going into too much detail, since the upper levels are simply data
> gathering, I've been rather liberal in my application of WITH
> (NOLOCK) on virtually every table that is used in the look up.
> Here's the reason why, initially the process took two hours to run
> and it didn't fully complete. Since my users are not in a Snickers
> commercial, I can hardly expect them to wait that long for this
> process. So I need to make it as fast as possible. I've gone through
> and changed all my CURSORS to select loops (mucho improvement) and
> also dumped #tempTables in favor of table variables (more
> improvement)... and then I added WITH (NOLOCK) on every normal (non
> temp or derrived - ie inner selects) table in all queries... I
> notice more improvement again. I also rearranged a few queries where
> some calculations were done breaking out the data into two parts and
> changed a LEFT JOIN to an INNER JOIN....
> IS the ANY thing else I can do to squeeze some performance out of this
> monster? I haven't run it through profiler yet, but that's the next
> obvious choice.
> The other half of the query is: is it possible to go too far with WITH
> (NOLOCK)? Or is what I've done reasonable?
> =chris
I think th eapproach you should reall ybe taking here is performance
tuning the SQL running in this long running batch. After you've
diagnosed all the SQL and you know things are running as fast as
possible, then start looking at hints as a possible way to improve
performance. As Hugo clearly demonstrates, not all business requirements
can tolerate a NOLOCK hint. Your business needs to decide if this is
tolerable or not.
Regarding your comment: "dumped #tempTables in favor of table variables
(more improvement)". I don't know what your temp/table vars look like,
but temp tables have some major performance advantages with larger data
sets because you can create indexes to support the queries run off the
tables. Also, if you're looping through the temp tables, pulling one row
at a time, use a SELECT TOP 1 to pull in the data for the row. If you're
joining to the temp tables or deleting from them, an index will likely
help.
David Gugick - SQL Server MVP
Quest Software|||Thanks David & Hugo... pretty much confirmed what I had already suspected. As
far as reading uncomitted data when using nolock, that's not a problem as the
data isn't updated until the very last step. I really wish I had the time to
go back and re-engineer the process properly in VB code rather than in SQL,
but it's a 5yr old process and if I change it now the account managers will
have a fit, and so will the client as they've been waiting long enough as it
is for this "to work" - it works as originaly built, but not like they want
it to be... ah, clients... where would we be w/o them. What I'm finding is
that 6 times out of 10 it's lightning fast... it's the 4 times that takes the
longest (the 4 times will take more time than the 6 did total.)
I'm not sure I could explain the process w/o giving any trade secrets (drat
those NDA's) or w/o making heads explode as I try to explain the industry, so
I won't bore people w/ the details.
Yes, idealy I wish I could do it in batches of select statement but the
business rules are getting in the way - I truly regret building this the way
I did 5 yrs ago... If I only knew then what I know now... but hindsight is
20/20 right?
At any rate, thanks for your help, I'm going to expore the possibility of
converting the process into VB code, and if I can do it in one day (the
project manager is out today) then I might attempt it. Otherwise, it'll just
have to go as it is for now.
Cheers,
Chris|||On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
>Thanks David & Hugo... pretty much confirmed what I had already suspected. As
>far as reading uncomitted data when using nolock, that's not a problem as the
>data isn't updated until the very last step.
Hi Chris,
Not by that process, it isn't. But are you equally sure that no other
users are accessing and changing the data at the same time?
(snip)
>At any rate, thanks for your help, I'm going to expore the possibility of
>converting the process into VB code, and if I can do it in one day (the
>project manager is out today) then I might attempt it. Otherwise, it'll just
>have to go as it is for now.
Good luck. And let us know if you need further assistance!
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" wrote:
> On Wed, 1 Mar 2006 07:05:28 -0800, CAnderson wrote:
> >Thanks David & Hugo... pretty much confirmed what I had already suspected. As
> >far as reading uncomitted data when using nolock, that's not a problem as the
> >data isn't updated until the very last step.
> Hi Chris,
> Not by that process, it isn't. But are you equally sure that no other
> users are accessing and changing the data at the same time?
>
Positive... The nature of the data being manipulated, as well as standard
business practicess in the industry, practicaly require that only one person
is going to be accessing and updating the data at any given time - even if
the process wasn't being automated.
> (snip)
> >At any rate, thanks for your help, I'm going to expore the possibility of
> >converting the process into VB code, and if I can do it in one day (the
> >project manager is out today) then I might attempt it. Otherwise, it'll just
> >have to go as it is for now.
> Good luck. And let us know if you need further assistance!
> --
> Hugo Kornelis, SQL Server MVP
>
I was able to port much of the business logic over to VB, leaving SQL to
just select queries and two action queries.... there was "some" improvement,
but it still isn't where I need to it be... I found several cases where I
was running through some logic where I didn't need to and short-circuited it
on that condition. And then I find out (from the QA dept no less) that the
Proj Manager has decided to ship it as it is with a note that says we are
working on the performance issue. Which means I can now take my time to do
this right rather than slapping it together like I did. Will wonders never
cease.
-Chris

possible to link 2 stored procedures to produce only 1 recordset?

Hello everybody!

trying to do the following:

-create a report in access project

-got 3 stored procedures which return data that shall be shown on report

-need one recordset as datasource (or can i use more than one here?)

Problem:

Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.

I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)

Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):

CREATE PROCEDURE IntegrateSPs AS

INTEGRATE
SP1,SP2,SP3
INTO myRecordset

Anything like this possible?

thx in advance,

KumaHow about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp|||How about this:

create a new "wrapper" procedure. that does the something like:

create #temp (with a buch of fields)

insert into #temp exec proc1
insert into #temp exec proc2

select *
from #temp

Well that would work nicely if the results of each sproc had the same number of columns and the same datatypes, and that each sproc only returns 1 result set...

Otherwise it won't...

Can you show me the result sets of each?|||Example:
SPs look somewhat like this (but huger with more calculation involved)

CREATE PROCEDURE SP1
@.PersonIDStart nvarchar (10),
@.PersonIDEnd nvarchar (10)

AS

SELECT
a + b + c AS fld1,
d + e + f AS fld2

FROM tbl
WHERE PersonID BETWEEN @.PersonIDStart AND @.PersonIDEnd

SP1 would return:
PersonID, Fld1, Fld2, Fld3

SP2:
PersonID, Fld4, Fld5, Fld6

SP3:
PersonID, Fld7, Fld8, Fld9

Of course there are more fields in each recordset. All FldX-fields are smallints.
PersonID is text.

SP1 to SP3 would get the same parameters passed for PersonID and retrieve a number of recordsets accordingly.

Reports will be created for each PersonID with all values from SP1 to SP3 for this PersonID on one sheet.

like this:

Results for PersonID: XYZ123

SP1 SP2 SP3
DimA 4 8 1
DimB 6 8 3
DimC 9 2 7

If i were to put data in a temp table or any go-between permanent table, it had to be one record per PersonID with all the data from the three SPs in it.

SPs are set up to retrieve recordsets only (SELECT). Would i be able to make them dump those into a table without altering them completely (i need the recordset approach elsewhere) and without having to duplicate them into INSERT SPs?

thx

Kuma|||You could use something like:CREATE PROCEDURE p_Wrapper
@.piPersonStart INT
, @.piPersonEnd INT
AS

CREATE TABLE #t1 (personid INT, f1 INT, f2 INT, f3 INT)
INSERT INTO #t1 EXECUTE sp1 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t2 (personid INT, f4 INT, f5 INT, f6 INT)
INSERT INTO #t2 EXECUTE sp2 @.piPersonStart, @.piPersonEnd

CREATE TABLE #t3 (personid INT, f7 INT, f8 INT, f9 INT)
INSERT INTO #t3 EXECUTE sp3 @.piPersonStart, @.piPersonEnd

SELECT *
FROM #t1
FULL JOIN #t2 ON (#t2.personid = #t1.personid)
FULL JOIN #t3 ON (#t3.personid = #t1.personid)

RETURN-PatP|||thx a lot.

hoped for something even simpler, but i can live with that.

Think I'll forego the CREATE TABLE thingy and make permanent tables that I empty after I'm finished. Dont like this temp table stuff.

glad i got the syntax for the INSERT statement!

ty again

Kuma|||Just hope the sproc is executed at the same time then...

I would suggest a table variable if you don't like temp tables...

My question to you then, is how BIG is the result set?

Because if it's not then temp is not a problem...

still I'd use a table variable...|||Just beware if you use permanent tables that you can only allow one user at a time to run the p_Wrapper procedure. Temp tables or table variables dodge that bullet.

-PatP|||thx for the info in the last two posts.

table variable sounds very good. i'll try that.

didnt think about the the issue of "one-user-at-a-time",
since this is a 1-user desktop DB (i'd make it multi-user
intranet, but user dont like it...). I'll change it anyway.
Who knows if and when it goes multi-user...

:)

Kuma

*edited for typo|||Good plan to allow for the possibility of multiple users! I can't count the number of databases I've set up that would "never" be used by more than one person, at least one of which is used by 5000+ people every day!

-PatP|||If structure of permanent tables is thought through then it is definitely an advantage over temp tables (BTW, table variables will not work...should I say why? ;) ).|||Temp tables work nicely though, and have every benefit needed for this problem.

-PatP|||rdjabarov: yes pls u should say why :)

ty|||rdjabarov: yes pls u should say why :)

tyThe source for your INSERT is EXECUTE|||To expand on rdjabarov's answer a bit, you can use INSERT INTO #temp when the source is an EXECUTE, but you can't use INSERT INTO @.temp with an EXECUTE. The temp table works, but the table variable does not because the syntax isn't accepted.

-PatP

possible Scope_Identity() problem

I have an ASP.NET application with a SQL Server 2000 backend, where two
pages fire off two different stored procedures. Each stored procedure
creates a new record in a particular table then uses Scope_Identity() to get
the id of the newly created record for adding it to a link table. The
problem I'm seeing appears to be Scope_Identity() behaving as thought it
were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at once
(different machines and browsers, not that it should matter), one procedure
appears to get the id of the record created by the other and essentially
steal its record.
Has anyone come across anything like this before?
thanks in advance,
--
jo inferisHi
Do you have code to reproduce this issue?
I only way I could think this happens is if both SP's are executed on the
same connection (which is unlikely).
Regards
Mike
"Jo Inferis" wrote:

> I have an ASP.NET application with a SQL Server 2000 backend, where two
> pages fire off two different stored procedures. Each stored procedure
> creates a new record in a particular table then uses Scope_Identity() to g
et
> the id of the newly created record for adding it to a link table. The
> problem I'm seeing appears to be Scope_Identity() behaving as thought it
> were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at on
ce
> (different machines and browsers, not that it should matter), one procedur
e
> appears to get the id of the record created by the other and essentially
> steal its record.
> Has anyone come across anything like this before?
> thanks in advance,
> --
> jo inferis
>
>|||Hi Jo,
Use IDENT_CURRENT('table_name') to get the Identity value. Because
IDENT_CURRENT returns the last identity value generated for a specific table
in any session and any scope.
@.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.
Regards
Sivakumar
"Jo Inferis" wrote:

> I have an ASP.NET application with a SQL Server 2000 backend, where two
> pages fire off two different stored procedures. Each stored procedure
> creates a new record in a particular table then uses Scope_Identity() to g
et
> the id of the newly created record for adding it to a link table. The
> problem I'm seeing appears to be Scope_Identity() behaving as thought it
> were @.@.IDENTITY, i.e. on the occasion when both procedures are fired at on
ce
> (different machines and browsers, not that it should matter), one procedur
e
> appears to get the id of the record created by the other and essentially
> steal its record.
> Has anyone come across anything like this before?
> thanks in advance,
> --
> jo inferis
>
>|||Subramaniam Sivakumar wrote:
> Use IDENT_CURRENT('table_name') to get the Identity value.
That's not going to help, both identities are created in the same table.
Obviously I didn't make that clear enough.
jo inferis|||Mike Epprecht (SQL MVP) wrote:
> Do you have code to reproduce this issue?
It's a little difficult to extract the code to reproduce it, and this is
only a vague possibility anyway. I was just wondering if there might have
been something i'd missed in the usage of Scope_Identity().

> I only way I could think this happens is if both SP's are executed on
> the same connection (which is unlikely).
I did think about that, but even then, the scope in each case should be
different, shouldn't it?
jo inferis|||Is scope_identity() is returning the same identity of the other stored proc
perhaps you're application is using connection pooling. I'm guessing that
would explain how 2 different pages would end up being in the same "scope".|||I don't see how this can happen but maybe this will help:
Wrap all inserts in your stored procedure in a transaction.
We haven't seen any code so you may be doing this anyway.
"Jo Inferis" <jo@.inferis.NOSPAM.gotadsl.co.uk> wrote in message
news:%23LYkTseNFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Mike Epprecht (SQL MVP) wrote:
> It's a little difficult to extract the code to reproduce it, and this is
> only a vague possibility anyway. I was just wondering if there might have
> been something i'd missed in the usage of Scope_Identity().
>
> I did think about that, but even then, the scope in each case should be
> different, shouldn't it?
> --
> jo inferis
>

Positioning of Stored Procedures

Hi all,

I have been facing this dilemma since when I started coding in asp.net 2.0. I can have Data Access Layer wherein I can write stored procedures to access the data from database. I can create data access object, data table and all other stuff. Also I can create stored procedure in SQL 2000 server, and then access them from the Data Access layer.

Which of the two method is preferable, and why. i have been searching net for answers to this question since long, but could not find anything.

All answers can contribute may be little but invaluable knowledge.

Thanks.

If security is critical, it's best to use stored procedures always because it lowers the attackable area of your database. I think this is what you are asking.

|||

I wanted to know, which is better:

1) Creating stored procedures in SQL Server 2000 and calling them in the Data access Layer, or may be in the code behind straight away.

2) Creating Table Adapters in Data Access Layer, and creating Table Adapter queries and accessing database or may be stored procedures within the Data Access Layer.

I have been informed that if you create the Table Adapter queries, they are equally secure as stored procedures; though i am not pretty confident about it.

Thanks again.

|||

Tell the truth, the issue is depending on your situation.

If you can connect database and your database permittion contol well, you need to do that on db.

if not, don't do that.

|||

Read this an argument against using SP. This will clarify your question as well

http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html

Hope that helps

|||

hello.

well, to be honest, sps aren't really something i'd advocate for crud behavior. in my opinion, using parametrized sql is the way to go. the performance/security bla bla that's has been used for several years is a myth and there are some posts out there that just show it. for instance, there's an old discussion between frans bouma and rob howard that started with a post from rob and a very well answer by frans. i'm putting only frans' post here since it is linked to rob's post.

http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

having said that, i'm not saying that there really isn't a place for sps; just saying that kmost of the time the argument for using them are pure myths!

|||

Hi,

I feel creating stored procedures in SQL Server 2000 and calling them in the Data access Layer is better choice. The book Titled :

"Database programming using C#, VB 2005 and SQL Server 2005", Chapter 10:Developing Components for three-tier applications explains this concept.

Let us say we have developed an three-tier application using SQL server 2000. In future, the same application should able to access/insert to Oracle database. In this situation, writing stored procedures at the server level is better.

I will find out further info on this matter.

|||

With the technologies ASP.NET 2 provide, you r always free to choose the way you like (depending upon your handy side). With the nearly the same amount of effort or even less you can still handle the shift to Oracle database.

But that said, your approach of choosing store procedure suppose to be a little faster in most cases.

|||

hello.

ask4jm:

But that said, your approach of choosing store procedure suppose to be a little faster in most cases

again, this is a known myth. read frans' post to see what i'm speaking about.

|||

You r absolutely rite Luis. Unless the database developers wants to give the data through specific routines hiding rest of the infra, store procedures can be completely avoided.

|||

db2Command cmd= db2Commant();

cmd.Connection=con;
param = new DB2Parameter("@.ClientId", DB2Type.Decimal, 8);
((DbCommand)base.dbSelectCommand[0]).Parameters.Add(param);

//Insert Command and parameters
string sqlInserCommand = "ProcCon";

base.dbInsertCommand = new DbCommand[1];
base.dbInsertCommand[0] = new DB2Command(sqlInserCommand);

param = new DB2Parameter("@.Name", DB2Type.VarChar, 100, "ClientName");
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);

param = new DB2Parameter("@.Cid", DB2Type.Int);
((DbCommand)base.dbInsertCommand[0]).Parameters.Add(param);