Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

Conditionally referring to fields

Hi,
I am using RS 2000. In a report, I have database field whose name keeps changing everytime based on some condition. Say, a stored proc returns a field Aug2005. The name of this field becomes Oct2005 on some other condition. How can I use this field in the layout (to drag n drop). By what name/alias could I refer to this field. I read that in RS 2005 there is an option like Fields.Items(index).Value to access the field conditionally but I tried it in RS 2000 to no avail. Please suggest a solution.
Thanks,
Biju.

When you use the Fields.Items syntax, what you are varying is the field name, not the underlying database query column name (called DataField in RDL). All columns returned by the query must be known and mapped in the RDL.

If you have a query that returns different columns, you need to add them both to the query and then conditionally switch between them.

sqlsql

Conditional Where

I have a stored proc that accepts several parameter, one being
relocation_id. If it is passed in I want to add it to the Where clase
AND rd.relocateID = @.relocation_id
Ive been trying to avoid dynamic sql and thought I could do something with
COALESCE but I cant seem to get same results for total if I left off the
AND
So if I run my query with the AND included from above I get 238,473 records.
If I do:
AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
207566. At this point @.relocation_id is NULL because it wasnt past in.
Im not even sure this is possible or are there other paths?
Thanks!is rd.relocateID nullable?|||The below takes advantage of @.relocation_id being NULL if it is not passed t
o
the stored procedure. If you have a default value, you will need to modify
accordingly.
SELECT [SomeColumns] FROM [SomeTable]
WHERE [OtherConditions]
AND CASE
WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
WHEN @.relocation_id IS NULL THEN 1
ELSE 0
END = 1
"Brian" wrote:

> I have a stored proc that accepts several parameter, one being
> relocation_id. If it is passed in I want to add it to the Where clase
> AND rd.relocateID = @.relocation_id
> Ive been trying to avoid dynamic sql and thought I could do something with
> COALESCE but I cant seem to get same results for total if I left off the
> AND
> So if I run my query with the AND included from above I get 238,473 record
s.
> If I do:
> AND rd.relocateID = COALESCE(@.relocation_id, rd.relocateID) I get
> 207566. At this point @.relocation_id is NULL because it wasnt past in.
> Im not even sure this is possible or are there other paths?
> Thanks!
>
>
>|||That worked perfect. Thanks. One follow up and not this is possible
I am taking the query that was in a Coldfusion page and making it an SP and
it was a conditional join
<cfif len(arg.degreeID)>LEFT OUTER JOIN resume_education ed ON ( rd.userID =
ed.userID )</cfif>
So if on the search page someone picked a Degree we would then need to join
in that table as well. Is the thinking here you should
just always make the join and then have the where clause be conditional like
below.
Thanks again!
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:6DAC0EEE-E8F0-4549-9A22-E3CD91D671E4@.microsoft.com...
> The below takes advantage of @.relocation_id being NULL if it is not passed
> to
> the stored procedure. If you have a default value, you will need to modify
> accordingly.
> SELECT [SomeColumns] FROM [SomeTable]
> WHERE [OtherConditions]
> AND CASE
> WHEN @.relocation_id IS NOT NULL AND rd.relocateid = @.relocation_id THEN 1
> WHEN @.relocation_id IS NULL THEN 1
> ELSE 0
> END = 1
>
> --
>
> "Brian" wrote:
>

Conditional stored procedure question

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @.iShowAll = 0
begin
(uid = @.myID) AND (registration_complete = 0)
end
else
begin
(uid = @.myID)
end

GO

Thanks,
davidyou can use a CASE statement but i do not know the syntax..heres another way of doing it

CREATE PROCEDURE Milestone_Get
(@.myID int, @.iShowAll int)
AS
if @.iShowAll = 0
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID AND registration_complete = 0
else
 SELECT uid, name, date, registration_confirmed FROM tbl_members where uid = @.myID
go

HTH|||Here's an example using a Case

SELECT uid, name, date, registration_confirmed FROM tbl_members
WHERE (uid = @.myID) AND registration_complete = CASE WHEN @.iShowAll = 0 THEN 0 ELSE registration_complete END

Conditional Order by?

Is there a way to do a conditional order by so that a user can give a parameter to a stored proc and it give back results sorted the way they want?

I want it so that the user can do 1 of 4 things,
* sort by "title" ascending,
* sort by "title" descending,
* sort by "synopsis" ascending,
* sort by "synopsis" descending

Can it be done? This is what I have but I get a syntax error:

select * from Blah

Order By
Case
when @.orderId = 1 then title asc
when @.orderId = 2 then title desc
when @.orderId = 3 then synopsis asc
when @.orderId = 4 then synopsis desc
end

Any help is greatly appreciated!

You need to split the query as ASC Query & Desc Query.

You can choose your order by columns (if it is single column) dynamically but Sorting Order you can't.

Use your query as follow as

if @.OrderId in (1,3)

select * from Blah
Order By
Case
when @.orderId = 1 then title
when @.orderId = 3 then synopsis
end asc

else

select * from Blah
Order By
Case
when @.orderId = 2 then title
when @.orderId = 4 then synopsis
end Desc

Or

You can use dynamic SQL

Declare @.SQL as NVarchar(1000)

Select @.SQL = N'select * from Blah

Order By ' +
Case
when @.orderId = 1 then 'title asc'
when @.orderId = 2 then 'title desc'
when @.orderId = 3 then 'synopsis asc'
when @.orderId = 4 then 'synopsis desc'
end

Exec (@.SQL)

|||Why don't you dynamically add the final order by clause to the select query string ?
I guess it is more clear to understand and much more flexible for any future changes.|||Thanks both for the replies, I didnt know it would be so detailed, I have a rather large select query that I am applying this to and I dont really want to create a string then execute it, it just looks messy to me. But I guess if I have no option I guess ill have to.

Thanks again|||In some cases I have done this:

SELECT ....,
SortOrder = Case when @.orderId = 1 then title
when @.orderId = 2 then REVERSE(TITLE)
when @.orderId = 3 then synopsis
when @.orderId = 4 then REVERSE(synopsis) END
ORDER BY SortOrder

The only issue with this is all the vars in the CASE must be the same type, or cast to a certain type.

|||

Easiest is to do below:

order by

case @.orderId when 1 then title end

, case @.orderId when 2 then title end desc

, case @.orderId when 3 then synopsis end

, case @.orderId when 4 then synopsis end desc

Note that you will get the best performance (assuming you have indexes on the column(s) and the plan can use it) if you use dynamic SQL to form the ORDER BY with required columns or use different SELECT statements. But in most cases, I have found that the above construct is easier to use, safe from SQL injection (dynamic SQL is prone to it if you are not careful) and with few conversions which single CASE expression requires.

|||Thanks all for the help with this topic, I think Umachandar's answer will fit my solution best.

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.
Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pro...05/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.
|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pro...05/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>
|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn this
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it. If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where the
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled plan.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead of
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creating
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
--
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:
>> Creating an index over a table causes its schema to change, and in turn
>> this causes queries that reference the table to be recompiled. So, the
>> short answer to "will the conditional index creation mess up the SQL
>> engine" is no.
>> SQL Server will first compile the procedure, and then start executing it.
>> If the schema of a table changes between the compilation and execution of
>> a statement referencing it, the statement will be recompiled.
>> Actually the behavior changed significantly between SQL 2000 and 2005. In
>> 2000, the recompilations would affect the entire batch or procedure. A
>> significant improvement has been made in SQL 2005 with statement-level
>> recompiles. As the name suggests, in SQL 2005 only the affected
>> statements are recompiled, rather than the entire batch or procedure.
>> For more information on the subject, we have a very good whitepaper here:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> The consequence of what you are doing is that if you interleave
>> executions of the procedure that do not cause the index creation with
>> others where the index is created, you will incur in a significant number
>> of recompiles, because the schema of the temp table won't match the
>> previous compiled plan. In SQL 2000, this will be exacerbated with the
>> lack of statement level recompiles. This might easily negate the benefits
>> of saving the overhead of creating an index when the table is small.
>> Also, creating an index on a small table is a low overhead operation
>> anyway. I'd consider always creating the index, and seeing if you can
>> make it part of the table definition altogether if applicable.

Conditional index creation

In my stored proc, I create a bunch of temp tables and when the temp
table exceeds 1000 rows, I create an index on one of the rows. So
basically:
insert #ttt
select * from bbb
if @.@.ROWCOUNT > 1000 begin
Create NonClustered Index #ttt_IX1 on #ttt (ID)
end
My question is whether the conditional creation of the index messes up
the SQL engine. Would it not create an optimal plan because it doesn't
know for sure whether an Index will be there?
Thanks.Creating an index over a table causes its schema to change, and in turn this
causes queries that reference the table to be recompiled. So, the short
answer to "will the conditional index creation mess up the SQL engine" is
no.
SQL Server will first compile the procedure, and then start executing it. If
the schema of a table changes between the compilation and execution of a
statement referencing it, the statement will be recompiled.
Actually the behavior changed significantly between SQL 2000 and 2005. In
2000, the recompilations would affect the entire batch or procedure. A
significant improvement has been made in SQL 2005 with statement-level
recompiles. As the name suggests, in SQL 2005 only the affected statements
are recompiled, rather than the entire batch or procedure.
For more information on the subject, we have a very good whitepaper here:
http://www.microsoft.com/technet/pr...005/recomp.mspx
The consequence of what you are doing is that if you interleave executions
of the procedure that do not cause the index creation with others where the
index is created, you will incur in a significant number of recompiles,
because the schema of the temp table won't match the previous compiled plan.
In SQL 2000, this will be exacerbated with the lack of statement level
recompiles. This might easily negate the benefits of saving the overhead of
creating an index when the table is small. Also, creating an index on a
small table is a low overhead operation anyway. I'd consider always creating
the index, and seeing if you can make it part of the table definition
altogether if applicable.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uVQnSFN5FHA.3540@.TK2MSFTNGP10.phx.gbl...
> In my stored proc, I create a bunch of temp tables and when the temp table
> exceeds 1000 rows, I create an index on one of the rows. So basically:
> insert #ttt
> select * from bbb
> if @.@.ROWCOUNT > 1000 begin
> Create NonClustered Index #ttt_IX1 on #ttt (ID)
> end
> My question is whether the conditional creation of the index messes up the
> SQL engine. Would it not create an optimal plan because it doesn't know
> for sure whether an Index will be there?
> Thanks.|||The indexes I was talking about are being created on a temp table that
was created inside a stored proc. Would that cause any repercussions?
Stefano Stefani [MSFT] wrote:
> Creating an index over a table causes its schema to change, and in turn th
is
> causes queries that reference the table to be recompiled. So, the short
> answer to "will the conditional index creation mess up the SQL engine" is
> no.
> SQL Server will first compile the procedure, and then start executing it.
If
> the schema of a table changes between the compilation and execution of a
> statement referencing it, the statement will be recompiled.
> Actually the behavior changed significantly between SQL 2000 and 2005. In
> 2000, the recompilations would affect the entire batch or procedure. A
> significant improvement has been made in SQL 2005 with statement-level
> recompiles. As the name suggests, in SQL 2005 only the affected statements
> are recompiled, rather than the entire batch or procedure.
> For more information on the subject, we have a very good whitepaper here:
> http://www.microsoft.com/technet/pr...005/recomp.mspx
> The consequence of what you are doing is that if you interleave executions
> of the procedure that do not cause the index creation with others where th
e
> index is created, you will incur in a significant number of recompiles,
> because the schema of the temp table won't match the previous compiled pla
n.
> In SQL 2000, this will be exacerbated with the lack of statement level
> recompiles. This might easily negate the benefits of saving the overhead o
f
> creating an index when the table is small. Also, creating an index on a
> small table is a low overhead operation anyway. I'd consider always creati
ng
> the index, and seeing if you can make it part of the table definition
> altogether if applicable.
>|||No functional repercussions - everything will work and nothing will break.
But like i wrote below, it will likely trigger a high number of recompiles,
which in turn can negatively affect performances.
It might be worth for you trying with a version of the stored procedure
where the index is always created, and compare performances in your workload
against the current version you have.
Stefano Stefani [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Frank Rizzo" <none@.none.com> wrote in message
news:uLP6xOV5FHA.3760@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The indexes I was talking about are being created on a temp table that was
> created inside a stored proc. Would that cause any repercussions?
>
> Stefano Stefani [MSFT] wrote:

Conditional Bulk insert

Hi

I am doing bulk insert as follows. The @.lastUpdate, @.filePath, @.formatFile comes as a parametes to stored proc

INSERT INTO Categories

SELECT CategoryId, @.LastUpdate FROM OPENROWSET

(

BULK @.filePath ,

FORMATFILE = @.formatFile,

FIRSTROW =2

)

AS a

This works fine for me.

But my new requirement is that i shouldn't insert the CategoryId if it exists

How can we have conditional bulk insert? i am using Bulk insert as the file might have millions of category Ids.

Please provide your inputs that executes much faster

Best Regards,

~Mohan Babu

Try

INSERT INTO Categories

SELECT CategoryId, @.LastUpdate FROM OPENROWSET

(

BULK @.filePath ,

FORMATFILE = @.formatFile,

FIRSTROW =2

)

AS a

where a.CategoryID not in (select distinct CategoryId from Categories )

Concurrent stored procs?

Hi all

I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.

What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)

(I am trying not to use a table to store whether the stored proc is running or not)

Thanks in advance.

RochanaOne can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Thanks Hans for quick reply.

I tried it but found some problems.
sp_getapplock needs an active transaction, without which it fails.
I am trying not to use Transactions because it slows down the system so badly.

Originally posted by HansVE
One can use sp_getapplock and sp_releaseapplock when invoking a sp.

Hans.|||Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||It works to some extent Hans.
The error checking never happens. It quits the process without displaying the error.
..
IF @.@.error <> 0
PRINT 'Proc already running'
...

Originally posted by HansVE
Could use global temporary table as semaphore.

CREATE TABLE ##proc_running(x int)
IF @.@.error <> 0
PRINT 'Proc already running'
ELSE BEGIN
...
DROP TABLE ##proc_running
END

Hans.|||Yes, the error is too serious to continue. If you cannot trap that error at the client, you could instead check for existence of the table object.

IF OBJECT_ID('tempdb.dbo.##proc_running') > 0|||sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..|||I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..

One problem with this is that two people could still start the proc simultaneously.

Hans.|||Enigma here is the scenario:
Say, the job is scheduled at 12noon everyday.
One of the bosses come and asks us to run that particular job becos they need to see the data on their screen. So one of the programmers of the team runs that job, or invoke the particular sp.
The sp takes about 10-15 mins to complete.
If it reaches 12noon while that particular sp is running, job kicks in and invoke that same sp again. (our sp is still running)
Thats why I want to check if the particular sp is already running or not, so that the sp wont run again.

Originally posted by Enigma
I am a little confused .. but isnt the job scheduled ... then why is it being run manually ... and even if it is being run manually ... why two people have been given the access ??|||Thanks Jonte..
Your suggestion worked !! ;)
I have to check the Event Info for my stored proc, thats running in the server.

I have pasted code to test what processes are currently running on the server on a db. May be you can run and see the results too

Thanks

declare @.spid bigint
declare crsr cursor read_only
for
select spid from master..sysprocesses where dbid>1 and kpid>1

open crsr
fetch next from crsr
into @.spid

while @.@.fetch_status<>-1
begin
dbcc inputbuffer(@.spid)
fetch next from crsr
into @.spid

end

close crsr
deallocate crsr

Originally posted by Jonte
sysprocesses holds info about all processes currently running on the server (master.sysprocesses)

dbcc inputbuffer spid tells you what a specific process is doing

The two combined (in some way ;)) should tell you if the proc is running already..

Hope it helps a little bit..