If my ASP.Net application crashes just after HOLDLOCK is issued?

Will the database get locked if my ASP.Net application that is calling a stored procedure in which a HOLDLOCK for table1 is issued to SQL Server, suddenly crashes just after the stored procedure is called?

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

|||So, it seems that thesafestpractice as far as preventing SQL Server being held up in above scenario, is to provide rollbacks/commits inside stored proecedures rather than in ASP.Net code through ADO.Net.Is that correct?|||

Is it safest? Yes.

However, like I said, I haven't tested myself to see how long it will take SQL Server to detect a dead connection. It could be seconds, it could be hours. And it only really makes a difference if you are running in a web-farm environment, or you have other applications (that don't also run on the web server machine) that need access to the table data in a quick manner -- AND you can't tolerate the database to be down should a machine totally crash (Which is pretty darn rare). I would suggest that if you are deploying to such an environment you test this out yourself.

If Exists Statement In Stored Procedure

Hello all!

Newbie question:

There appears to be something wrong with this syntax in SQL Server 2000:

CREATE PROCEDURE spAddNewUser

@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT

AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

I can't get past the last "if exists" statement, without getting a syntax error when checking syntax. I get "ERROR: Incorrect Syntax near ')'.

I'm sure it's a very simple mistake...

Thanks in advance for any help :-)I think the IF statement is expecting some more code.

if i add the code

begin
print 'yes'
end

after your code then i don't get any parse errors.

What extra code do you want to put in, as your stored procedure does not do anything at the mo :confused:|||I don't know if the code you have shown is what you have, but if it is you are missing the brackets around the paramter list.

CREATE PROCEDURE spAddNewUser
(
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
)
AS

IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)

Also don't forget to use BEGIN and END if you need to run a block of code when the IF statment is true.|||Try this way:

CREATE PROCEDURE spAddNewUser
@.UserName varchar (50),
@.Password varchar (10),
@.NewUserID int = null OUTPUT
AS
IF EXISTS (SELECT * FROM Security WHERE UserName = @.UserName)
select 'exists!'
ELSE
select 'not exists!'|||all of you are correct...I guess it was just waiting for more info (what happens AFTER the IF EXISTS statement).

I just went ahead and completed the code and it was fine.

<blush>

:-)

If Exists capture value returned from stored proc

I have a stored proc with a query which checks whether an identicalvalue is already in the database table. If so, it returns a value of 1.How do I caputure this value in an asp.net page in order to display amessage accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE...
)
Return 1
Else
...INSERT INTO... code here.Did this exact thing for someone already in the past 2 weeks, search the forums.|||

Motley wrote:

Did this exact thing for someone already in the past 2 weeks, search the forums.

Thanks, will do so.sql

If Else If condition failing

This is only the second stored procedure that I've written, and I'm
having some issues with a conditional statement that I can't figure
out. The statement has a conditional If statement with two Else If's
that checks for a passed parameter's value (an integer I pass when
executing the SP via ASP). Here is basically what I have:
-- @.SET STATUS has 3 valid values:
-- 1 - Don't set status
-- 2 - Set inactive
-- 3 - Set active
CREATE PROCEDURE dbo.sp_SomeProcedure
@.SET_STATUS int
AS
SET NOCOUNT ON
IF @.SET_STATUS = 1
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE
BEGIN
INSERT STATEMENT
END
ELSE IF @.SET_STATUS = 2
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE IF @.SET_STATUS = 3
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
GO
When @.SET_STATUS is set to either 1 or 2, the sequel statements run
fine and the corresponding row gets updated or inserted accordingly.
If however the @.SET_STATUS is passed as 3, the procedure executes fine,
but the update statement is not run.
I've manually plugged in the "exists" condition and the update
statement for this part of the procedure and they fire off correctly
when run in QA. I'm at a loss. I've pretty much determined that it is
failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
why since it passes the syntax check.
Am I missing something here? Thanks in advance!Here's how I recommend the structure:
IF @.SET_STATUS = 1
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 2
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 3
BEGIN
.. do stuff ...
END
There's no need for ELSE, and you should always wrap the result of an IF
statement in BEGIN/END.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||The ELSE IF @.SET_STATUS = 3 is the else of the IF EXISTS() under
@.SET_STATUS = 2
So it's never actually getting to the IF @.SET_STATUS = 3 statement.
Add BEGIN..END around the IF EXISTS() under each ELSE IF
e.g.
ELSE IF @.SET_STATUS = 2
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
ELSE IF @.SET_STATUS = 3
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
tsigler@.gmail.com wrote:
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||Aaron, you're a rock star! It must not have liked the second "else if"
and after updating like you suggested, everything works like a champ.
Thanks!!!|||Thanks Trey ;)|||Depending on what you're doing in the 3 updates, you may be able to combine
these into 1 statement.
> IF @.SET_STATUS = 1 AND NOT EXISTS (SELECT STATEMENT)
> BEGIN
> INSERT STATEMENT
> END
ELSE
> BEGIN
> UPDATE STATEMENT
> END
Explain what the 3 updates do in your procedure and maybe we can suggest a
better way.
In this part:
> ELSE IF @.SET_STATUS = 2 (or 3)
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
I don't think that you need to check IF EXISTS unless you are performing an
action if this is FALSE.
Your update statement should affect 0 rows if it doesn't exist.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>

IF ELSE alternative for stored procedure

Hi,

I'm trying to create a stored procedure that checks to see whether the parameters are NULL. If they are NOT NULL, then the parameter should be used in the WHERE clause of the SELECT statement otherwise all records should be returned.

sample code:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50), @.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)ASBEGINSET NOCOUNT ONIF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitle;ELSE IF @.ProjectTitleISNOT NULL AND @.ProjectManagerIDISNOT NULL AND @.DeptCodeISNULL AND @.ProjIDISNULL AND @.DateRequestedISNULL AND @.DueDateISNULL AND @.ProjectStatusIDISNULLSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE ProjectTitle = @.ProjectTitleAND ProjectManagerID = @.ProjectManagerID;ELSESELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_Project;

I could do this using IF-ELSE but that would require a ridiculous amount of conditional statements (basically 1 for each combination of NULLs and NOT NULLs). Is there a way to do this without all the IF-ELSEs?

Thanks.

it will be easier to dynamically build your sql string, while you'll still have to use IF-ELSE you won't have to check for each and every condition combination, just check for each parameter. for example...

CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitlevarchar(300), @.ProjectManagerIDint, @.DeptCodevarchar(20), @.ProjIDvarchar(50),
@.DateRequesteddatetime, @.DueDatedatetime, @.ProjectStatusIDint)
AS
BEGIN
SET NOCOUNT ON

DECLARE @.strSQL varchar(4000)

SET @.strSQL = "SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID "
SET @.strSQL = @.strSQL + "FROM dbo.tbl_Project "

SET @.strSQL = @.strSQL + "WHERE "

IF @.ProjectTitle IS NOT NULL

BEGIN

SET @.strSQL = @.strSQL + "ProjectTitle = " + @.ProjectTitle

END

IF @.ProjectManagerId IS NOT NULL

BEGIN

SET @.strSQL = @.strSQL + " AND ProjectMangerId = " + @.ProjectMangerId

END

<conditions for each param>

EXEC(@.strSQL)

GO

|||

There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.

CREATEPROCEDURE [dbo].[GetProjectInfo]

(

@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,

@.ProjID

varchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)

AS

BEGIN

SET

NOCOUNTONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)AND(ProjID= @.ProjIDOR @.ProjIDISNULL)AND(.... [rest of parameters])

END

|||SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetProjectInfo]
(@.ProjectTitle varchar(300), @.ProjectManagerID int, @.DeptCode varchar(20), @.ProjID varchar(50),
@.DateRequested datetime, @.DueDate datetime, @.ProjectStatusID int)
AS
SET NOCOUNT ON
IF DATALENGTH(RTRIM(@.ProjectTitle)) = 0 SET @.ProjectTitle = NULL
IF DATALENGTH(RTRIM(@.ProjectManagerID)) = 0 SET @.ProjectManagerID = NULL
IF DATALENGTH(RTRIM(@.ProjectStatusID)) = 0 SET @.ProjectStatusID = NULL
IF DATALENGTH(RTRIM(@.DeptCode)) = 0 SET @.DeptCode = NULL
IF @.DueDate >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DueDate = NULL
IF @.DateRequested >= CONVERT(DATETIME, '9999-12-31 00:00:00') SET @.DateRequested = NULL
SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID
FROM dbo.tbl_Project
WHERE ProjectTitle = COALESCE(@.ProjectTitle, ProjectTitle)
AND ProjectManagerID = COALESCE(@.ProjectManagerID, ProjectManagerID)
AND ProjectStatusID = COALESCE(@.ProjectStatusID, ProjectStatusID)
AND DeptCode = COALESCE(@.DeptCode, DeptCode)
AND DateRequested = COALESCE(@.DateRequested, DateRequested)
AND DueDate = COALESCE(@.DueDate, DueDate)|||

The beauty of the S.P. I just posted is that it remains very simple no matter what combination of search is used. For unused string crriteria, use ''. For Integer use use 0. For dates pass a date of 31/Dec/9999.

The idea was suggested some years ago to me by Joe Celko.

|||

Woah.

Thanks for all the quick replies.

I'm away from my development machine right now, but I'll try the solutions in a few hours and let you know how it works out.

Thanks again.

|||I would recommend user "Connect"'s solution. Too many IF loops can screw up the query plan.|||

Connect:

There is a better way than using lots of IF and ELSE statements. Rewrite your stored proecedure so that it doesn't matter what parameters are passed it will always work. eg.

CREATEPROCEDURE [dbo].[GetProjectInfo]

(@.ProjectTitlevarchar(300)=NULL, @.ProjectManagerIDint=NULL, @.DeptCodevarchar(20)=NULL,

@.ProjIDvarchar(50)=NULL, @.DateRequesteddatetime=NULL, @.DueDatedatetime=NULL, @.ProjectStatusIDint=NULL)

AS

BEGIN

SETNOCOUNTON

SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusID

FROM dbo.tbl_Project

WHERE(ProjectTitle= @.ProjectTitleOR @.ProjectTitleISNULL)

AND(ProjectManagerID= @.ProjectManagerIDOR @.ProjectManagerIDISNULL)

AND(DeptCode= @.DeptCodeOR @.DeptCodeISNULL)

AND(ProjID= @.ProjIDOR @.ProjIDISNULL)

AND(.... [rest of parameters])

END

I was going over the code you posted, and it occurred to me that I made a *slight* error.

The ProjID is actually of the form DeptCode-Number (e.g. ACCT-1) and I don't have the DeptCode field in my tbl_Project.

Basically, I want to do something like:

CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSETNOCOUNT ONSELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND ((IF @.DeptCodeISNOT NULL ProjID = @.DeptCode +'-' +'%')ELSE ProjID = @.ProjIDOR @.ProjIDISNULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END

which is to say,

if @.DeptCode is not null, ProjID = @.DeptCode + '-' + (any number)

else ProjID = @.ProjID

I tried the code I posted, but understandably I get errors of Incorrect syntax near keyword IF an near ProjID.

Thanks again.

|||

Hi,

I was wondering if someone could help me out with the problem of implementing

IF @.DeptCode IS NOT NULL

ProjID = @.DeptCode + '-' + (any number)

ELSE ProjID = @.ProjID

in the code given by Connect.

Thanks.

|||

Try something like this:

CREATE PROCEDURE [dbo].[GetProjectInfo](@.ProjectTitlevarchar(300) =NULL, @.ProjectManagerIDint =NULL, @.DeptCodevarchar(20) =NULL,@.ProjIDvarchar(50) =NULL, @.DateRequesteddatetime =NULL, @.DueDatedatetime =NULL, @.ProjectStatusIDint =NULL)ASBEGINSET NOCOUNT ONDeclare @.nintIF @.DeptCodeISNOT NULLSET @.Deptcode = @.Deptcode +'-' +convert(varchar,@.n)SELECT ProjID, ProjectTitle, ProjectDetails, ProjectManagerID, RequestedBy, DateRequested, DueDate, ProjectStatusIDFROM dbo.tbl_ProjectWHERE (ProjectTitle = @.ProjectTitleOR @.ProjectTitleISNULL)AND (ProjectManagerID = @.ProjectManagerIDOR @.ProjectManagerIDISNULL)--AND (DeptCode = @.DeptCode OR @.DeptCode IS NULL)AND (ProjID =CaseWHEN @.DeptcodeISNOT NULLTHEN @.DeptCodeELSE @.ProjIDEND)--AND ((IF @.DeptCode IS NOT NULL ProjID = @.DeptCode + '-' + '%') ELSE ProjID = @.ProjID OR @.ProjID IS NULL)-- OR @.DeptCode IS NULL))AND (DateRequested = @.DateRequestedOR @.DateRequestedISNULL)AND (DueDate = @.DueDateOR @.DueDateISNULL)AND (ProjectStatusID = @.ProjectStatusIDOR @.ProjectStatusIDISNULL)END

|||

Hi,

Thanks for the reply.

The code you posted doesn't seem to work. Now I get 0 rows returned when using any of the parameters.

|||

I did not set any value for @.n. You just mentioned "number" but didnt say what number? does it come from a lookup table/user? For example:

SET @.n = 4

right before the check for NULL on @.Deptcode.

|||

oh. What I meant by number was, any number using a wildcard. The only wildcard I know for sql is '%', that's why if you see my example code, I did the following

ProjID = @.DeptCode + '-' + '%'

I don't know if that would work, but it shows the general idea.

|||I dont understand..can you provide some sample parameters and how you expect the ProjID to turn out?|||

sure. Let's say I run the code given below:

DECLARE@.return_valueintEXEC@.return_value = [dbo].[GetProjectInfo]@.ProjectTitle =NULL,@.ProjectManagerID =NULL,@.DeptCode ='BAT',@.ProjID =NULL,@.DateRequested =NULL,@.DueDate =NULL,@.ProjectStatusID =NULLSELECT'Return Value' = @.return_valueGO

I would want that to return all records where the ProjID = BAT-*

That is, all records that have the word BAT as the first part of the ProjID. 

if condition with stored procedure

i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @.id or order = @.fdfdfd.

i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.First of all, i am not clear with your question.

If you want to insert based on some condition, then specify the insert statment inside an if statement.

Regards
Ravi

IF @@ROWCOUNT > 200 Return Nothing

Hi, I am trying to write a stored proc that will return data for use on a
webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How do
I just return an error and no data?
TIAHi
Look up RAISERROR in SQL Server BOL
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"AlCoast" wrote:

> Hi, I am trying to write a stored proc that will return data for use on a
> webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How
do
> I just return an error and no data?
> TIA|||Ok. Thank you. Will do
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Look up RAISERROR in SQL Server BOL
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "AlCoast" wrote:
>|||Try,
...
if (select count(*) from ...) > 200
raiserror('more than 200.', 16, 1)
else
select c1, ..., cn from ...
...
AMB
"AlCoast" wrote:

> Hi, I am trying to write a stored proc that will return data for use on a
> webpage. I would like the SP to return nothing if the ROWCOUNT > 200. How
do
> I just return an error and no data?
> TIA

If / Else Stored Proc -- What Am I doing wrong?

Bellow is the stored procedure. I wanted to do an If / Else statement. I'm getting a syntax error that something is wrong around my Begin / Else statements. If anyone knows what is wrong I would greatly appriciate it.

Thanks in advance as always.

RB

<code>

Create Proc UpdateFundsAndTotals
AS
IF (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPledges.fundID AND FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END
ELSE (Select FundsAndTotals.fundID, FundsAndTotals.TotalPledges, TotalPledges.fundID, TotalPledges.TotalPledges From FundsAndTotals, TotalPledges Where FundsAndTotals.fundID = TotalPayments.fundID AND FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)
Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
END
Else
END

</code>

what you have is pseudo code..the syntax is :

IF (Select somecolumnfrom sometablewhere condition2 <>select anothercolumnfrom anothertablewhere condition2)BeginUpdate sometable1set somecol= somevaluewhere theconditionEndelsebeginUpdate sometablesset somecol= somevaluewhere theconditionendNote that when you are checking for A <> B in the IF statement A and B should have only 1 values each..you cannot have a result set comparing to another result set.

|||

Are you sure you can do <code>if <condition> else <condition></code> ? I think you need an extra "if" block

|||

This isn't valid SQL:

Begin
Update FundsAndTotals
Set FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
END

That's one issue. You seem to be trying to piggy-back onto the selects in the IF statement. They're separate statements; that doesn't work.

|||I don't 100% understand your IF/ELSE needs, but try something like this:
CREATE PROC UpdateFundsAndTotals
AS
IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID WHERE FundsAndTotals.TotalPledges != TotalPledges.TotalPledges)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPledges = TotalPledges.TotalPledges
FROM
FundsAndTotals
INNER JOIN
TotalPledges ONFundsAndTotals.fundID = TotalPledges.fundID
WHERE
FundsAndTotals.TotalPledges != TotalPledges.TotalPledges

END

ELSE

BEGIN

IFEXISTS(SELECT NULLFROM FundsAndTotals INNER JOINTotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID WHERE FundsAndTotals.TotalPayments != TotalPayments.TotalPayments)

BEGIN

UPDATE
FundsAndTotals
SET
FundsAndTotals.TotalPayments = TotalPayments.TotalPayments
FROM
FundsAndTotals
INNER JOIN
TotalPayments ONFundsAndTotals.fundID = TotalPayments.fundID
WHERE
FundsAndTotals.TotalPayments != TotalPayments.TotalPayments

END

END

|||wow Terri you have tremendous patience..you go to exceptional levels to understand a user's requirements..I always admire you for that.. Good job.|||

Thanks a lot Terri just wanted to post that your suggestion did the trick.

Thanks again.

RB

|||I am glad that helped! It seems to me that you don't really need the IF(EXISTS) parts of that since the WHERE condition in the UPDATEstatement should take care of the filtering.

IF / ELSE -- Update Stored procedure

What am I doing wrong in this code:
<CODE>
Select Results.custID
From Results
If (Results.custID = DRCMGO.custID)
Begin
Update Results
SET Results.DRCMGO = 'Y'
END
ELSE
Begin
Update Results
SET Results.DRCMGO = 'N'
END
<CODE>
I'm trying to do an IF / ELSE statement:
-- if the custIDs in my Results table and my DRCMGO table match then I want to set DRCMGO to Y
-- if they don't match I want to set it to N
What is wrong with this syntax. If someone could let me know i would greatly appriciate it (I'm doing it as SQL Books Online is telling me to)
Thanks in advance everyone.
RB

In the if statment you are asking if the entire column results.custID = drcmgo.custID.
I think you want something like this
update results
set results.drcmgo = 'y'
from results
join drcmgo on drcmgo.custid = results.custid
update results
set results.drcmgo = 'n'
from results, drcmgo
where drcmgo.cust <> results.custid

IDENTITY values in a stored procedure

Hi All,
This is my stored procedure

CREATE PROCEDURE testProc AS
BEGIN
CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT)
INSERT INTO #tblTest(Col1)
SELECT colA FROM tableA ORDER BY colA

END

This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5....
or is there any chance of ID column having values like 1,2, 4, 6,7,8...

Please reply...
qaAs long as you don't do any deletes from your temp table, your identity column should remain sequential with no gaps.|||Thanks for your quick response.sql

identity in stored procedures

I have a stored procedure called sp_Insert_System_Header which inserts a record with an identity. I save this as id.
Then in the sp_Customer_Complaint_Entry stored procedure I want to use this field as a parameter for the case_id field.
When I run I get no errors But also no records get inserted into either table
Any Ideas.
CREATE PROCEDURE sp_Insert_System_Header
(
@.System_Type_IDint,
@.Priority_IDint,
@.PCAR_Manager_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_To_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
@.ID int OUTPUT
)
AS
INSERT INTO HEADER(
System_Type_ID,
Priority_ID,
PCAR_Manager_ID,
Opportunity_ID,
Status_ID,
Initiated_By_ID,
Location_ID,
OtherLocation,
Initiated_On,
Assigned_By_ID,
Assigned_To_ID,
Assigned_On,
Resolved_On,
Closed_BY_ID,
Closed_On,
Description,
Immediate_Actions,
Cause_Type_ID,
Actual_Cause_Description,
Corrective_Description
)
VALUES(
@.System_Type_ID,
@.Priority_ID,
@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.Assigned_To_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
)
Select @.ID = @.@.Identity
GO
CREATE PROCEDURE sp_Customer_Complaint_Entry
(
@.System_Type_IDint,
@.Priority_IDint,
@.Opportunity_IDint,
@.Status_IDint,
@.Initiated_By_IDint,
@.Location_IDint,
@.Other_Locationvarchar(100),
@.Initiated_OnDateTime,
@.Assigned_By_IDint,
@.Assigned_OnDateTime,
@.Resolved_OnDateTime,
@.Closed_BY_IDint,
@.Closed_OnDateTime,
@.Descriptionvarchar(2000),
@.Immediate_Actionsvarchar(2000),
@.Cause_Type_IDint,
@.Actual_Cause_Descriptionvarchar(2000),
@.Corrective_Descriptionvarchar(2000),
--THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
--SYSTEM ONLY
@.BusinessUnit_IDint,
@.RMANumbervarchar(50),
@.Product_Codevarchar(50),
@.Product_Namevarchar(100),
@.Customer_Numbervarchar(50),
@.Customer_Namevarchar(50),
@.Lot_Numbervarchar(50),
@.PO_Numbervarchar(50),
@.ID int OUTPUT
)
AS
Declare @.@.CASE_ID int,
@.@.PCAR_Manager_ID int
--FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
EXEC
@.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
--ADD NEW RECORD TO SYSTEM HEADER
EXEC
@.@.CASE_ID = sp_Insert_System_Header
@.System_Type_ID,
@.Priority_ID,
@.@.PCAR_Manager_ID,
@.Opportunity_ID,
@.Status_ID,
@.Initiated_By_ID,
@.Location_ID,
@.Other_Location,
@.Initiated_On,
@.Assigned_By_ID,
@.@.PCAR_Manager_ID,
@.Assigned_On,
@.Resolved_On,
@.Closed_BY_ID,
@.Closed_On,
@.Description,
@.Immediate_Actions,
@.Cause_Type_ID,
@.Actual_Cause_Description,
@.Corrective_Description
--ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
INSERT INTO Customer_Complaint_System(
Case_ID,
BusinessUnit_ID,
RMANumber,
Product_Code,
Product_Name,
Customer_Number,
Customer_Name,
Lot_Number,
PO_Number)
VALUES(
@.@.CASE_ID,
@.BusinessUnit_ID,
@.RMANumber,
@.Product_Code,
@.Product_Name,
@.Customer_Number,
@.Customer_Name,
@.Lot_Number,
@.PO_Number
)
SELECT @.ID = @.@.CASE_ID
GO
I think the problem is incorrect usage of OUTPUT parameters.
The following is how you're doing it, which is actually appropriate syntax
for a RETURN value:
DECLARE @.myVariable INT
EXEC @.myVariable = my_Stored_Proc @.params, ...
For OUTPUT parameters, on the other hand, you do it this way:
DECLARE @.myVariable INT
EXEC my_Stored_Proc @.params, ..., @.myVariable OUTPUT
Does that make sense?
RETURN values, by the way, can only be of the INT datatype, and of course
you can only have one of them. OUTPUT params, on the other hand, can be of
any scaler datatype and you can have as many as you want. So they're quite
useful...
Also, two other comments: A) It's recommended that you not use sp_ to
prefix stored procedures as this is the prefix used for system stored
procedures and will cause a small performance penalty due to the server
looking for your stored procedure in the master database before looking
locally. B) You should probably not use @.@. to prefix variables, as that's
the prefix for system variables. Just a code readability issue.
"jat14" <anonymous@.discussions.microsoft.com> wrote in message
news:D140EA0D-E488-429E-9575-9942D9974D7E@.microsoft.com...
> I have a stored procedure called sp_Insert_System_Header which inserts a
record with an identity. I save this as id.
> Then in the sp_Customer_Complaint_Entry stored procedure I want to use
this field as a parameter for the case_id field.
> When I run I get no errors But also no records get inserted into either
table
> Any Ideas.
>
> CREATE PROCEDURE sp_Insert_System_Header
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.PCAR_Manager_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_To_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> @.ID int OUTPUT
> )
> AS
> INSERT INTO HEADER(
> System_Type_ID,
> Priority_ID,
> PCAR_Manager_ID,
> Opportunity_ID,
> Status_ID,
> Initiated_By_ID,
> Location_ID,
> OtherLocation,
> Initiated_On,
> Assigned_By_ID,
> Assigned_To_ID,
> Assigned_On,
> Resolved_On,
> Closed_BY_ID,
> Closed_On,
> Description,
> Immediate_Actions,
> Cause_Type_ID,
> Actual_Cause_Description,
> Corrective_Description
> )
> VALUES(
> @.System_Type_ID,
> @.Priority_ID,
> @.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.Assigned_To_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> )
> Select @.ID = @.@.Identity
> GO
>
> CREATE PROCEDURE sp_Customer_Complaint_Entry
> (
> @.System_Type_ID int,
> @.Priority_ID int,
> @.Opportunity_ID int,
> @.Status_ID int,
> @.Initiated_By_ID int,
> @.Location_ID int,
> @.Other_Location varchar(100),
> @.Initiated_On DateTime,
> @.Assigned_By_ID int,
> @.Assigned_On DateTime,
> @.Resolved_On DateTime,
> @.Closed_BY_ID int,
> @.Closed_On DateTime,
> @.Description varchar(2000),
> @.Immediate_Actions varchar(2000),
> @.Cause_Type_ID int,
> @.Actual_Cause_Description varchar(2000),
> @.Corrective_Description varchar(2000),
> --THESE PARAMETERS ARE FOR CUSTOMER COMPLAINT
> --SYSTEM ONLY
> @.BusinessUnit_ID int,
> @.RMANumber varchar(50),
> @.Product_Code varchar(50),
> @.Product_Name varchar(100),
> @.Customer_Number varchar(50),
> @.Customer_Name varchar(50),
> @.Lot_Number varchar(50),
> @.PO_Number varchar(50),
> @.ID int OUTPUT
> )
> AS
> Declare @.@.CASE_ID int,
> @.@.PCAR_Manager_ID int
> -- FIND OUT WHO IS THE PCAR MANAGER FOR CUSTOMER COMPLAINT SYSTEM
> EXEC
> @.@.PCAR_Manager_ID = sp_getPCARID @.System_Type_ID, @.Initiated_By_ID
> -- ADD NEW RECORD TO SYSTEM HEADER
> EXEC
> @.@.CASE_ID = sp_Insert_System_Header
> @.System_Type_ID,
> @.Priority_ID,
> @.@.PCAR_Manager_ID,
> @.Opportunity_ID,
> @.Status_ID,
> @.Initiated_By_ID,
> @.Location_ID,
> @.Other_Location ,
> @.Initiated_On,
> @.Assigned_By_ID,
> @.@.PCAR_Manager_ID,
> @.Assigned_On,
> @.Resolved_On,
> @.Closed_BY_ID,
> @.Closed_On,
> @.Description,
> @.Immediate_Actions,
> @.Cause_Type_ID,
> @.Actual_Cause_Description,
> @.Corrective_Description
> -- ADD NEW RECORD TO CUSTOMER COMPLAINT TABLE
> INSERT INTO Customer_Complaint_System(
> Case_ID,
> BusinessUnit_ID,
> RMANumber,
> Product_Code,
> Product_Name,
> Customer_Number,
> Customer_Name,
> Lot_Number,
> PO_Number)
> VALUES(
> @.@.CASE_ID,
> @.BusinessUnit_ID,
> @.RMANumber,
> @.Product_Code,
> @.Product_Name,
> @.Customer_Number,
> @.Customer_Name,
> @.Lot_Number,
> @.PO_Number
> )
> SELECT @.ID = @.@.CASE_ID
>
> GO
>

Identity field with a query ?

I want to kown if a field is an identity (counter) using a query or a stored procedure ?
ThanksReturns all identity columns in a database:select sysobjects.name as tablename,
syscolumns.name as IdentityColumnName
from sysobjects
inner join syscolumns on sysobjects.id = syscolumns.id
where syscolumns.autoval is not null

Identity Field

Please, How can I get the value of the identity field of the register that I was including in the data base. I am using a stored procedure in SQLSERVER in a asp .net application and I need to show that for the user, it′s like the number of the reclamation.In your stored proc, create an OUTPUT parameter. return the identity value through the OUTPUT parameter. Here's a sample:

CREATE PROC dbo.usp_Somestoredproc (
@.someparam1 int
,@.someparam2 varchar(50)
,@.retval int OUTPUT
)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO yourTable
(somecol1,somecol2)
VALUES (@.someparam1, @.someparam2)
SELECT @.retval=SCOPE_IDENTITY()

SET NOCOUNT OFF
END
From your ASP.NET Code you can catch the value returned by declaring an output parameter.

|||Hi,
thanks. I've tested and it returned the value of the identitiy.

Identity constraint - Drop Create dynamically?

Greetings all,

I have an identity field that I need to drop the constraint and recreate it inside a stored procedure. Actually if there is a better way to do what I'm trying to do, I'm all ears.

My concern is that the 2^31 will eventually be exceeded on the int declaration for this field and I'm not using the field for anything other than to identify uniqueness for a specific task.

What I would like to do is re-number this field each time the table is populated. (not remember the last index)Once I'm finished with the table, I'd like the constraint to be removed.

Any thoughts?

Adamus

The only 'real' option is to drop the existing IDENTITY field, and recreate a new one.|||

I was leaning that way.

Thank you,

Adamus

Identity column value increments by 2 rather than 1 on insert

I have an issue with a stored procedure activation on a service broker queue. The activation stored procedure simply RECEIVES the top message and then INSERTs a row into a table with an identity INT column. Each row inserted has the identity column value incremented by 2 rather than 1. Only one row is inserted in the table.

If the activation is set to OFF and then manually calling the original stored procedure the insert works fine and the identity column value is only incremented by one.

Do you have any suggestions on why the identity column value increments by 2?

Thanks.

I tested what you describe and the identities are implemented by one, as expected.

Can you post the code of your procedure?

|||

Here are some simple test scripts to create the service broker logic:

-- Configure Queue

CREATE MESSAGE TYPE TestMessage VALIDATION = NONE

CREATE CONTRACT TestContract (TestMessage SENT BY INITIATOR)

CREATE QUEUE QueueTest WITH STATUS = ON, ACTIVATION ( PROCEDURE_NAME = PTest,

MAX_QUEUE_READERS = 1, EXECUTE AS 'dbo' )

CREATE SERVICE ServiceTest ON QUEUE QueueTest ( TestContract )

-- Send procedure

CREATE PROCEDURE [dbo].[PTestLogMessage]

@.msg_details varchar(390)

AS

BEGIN

DECLARE @.handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.handle FROM SERVICE ServiceTest TO

SERVICE 'ServiceTest' ON CONTRACT TestContract WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @.handle MESSAGE TYPE TestMessage ( @.msg_details )

END

-- Activation procedure

CREATE PROCEDURE [dbo].[PTest]

AS

DECLARE @.msg VARCHAR(390)

BEGIN

RECEIVE TOP(1) @.msg = message_body

FROM dbo.QueueTest

-- This insert results in the identity column value incrementing by 2

INSERT INTO t_test ( msg )

VALUES (@.msg)

END

-- table

CREATE TABLE [dbo].[t_test]([id] [int] IDENTITY(1,1) NOT NULL,

[msg] [varchar](390) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_t_test] PRIMARY KEY CLUSTERED

([id] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

|||

Your activated procedure is not guaranteed to RECEIVE a message each time it's activated. In other words, you should expect that RECEIVE returns an empty rowset. In fact, if you do no loop inside your procedure until you hit an empty resultset (hit 'bottom' of queue), the activation launcher will loop for you, and call the procedure again. So the activated procedure code you showed is pretty much guaranteed to be called twice in a row for each message enqueuef, once because it was activated and once because the activation did not see that you hit an empty RECEIVE. The seconf time you will hit one. Because you do not check whether the RECEIVE returned or not an empty rowset (@.@.ROWCOUNT > 0) you insert twice for each message, so the identity is incremented by two.

Also you should project into RECEIVE the conversation_handle and message_type_name and repsond properly to 'EndDialog' and 'Error' messages, and the conversations have to be eventually ended, otherwise the sys.conversation_endpoints will grow out of control.

identity column rollback?

I am using a stored procedure to insert data to a table.

If there is any error then i rollback the transaction. this works fine.

but the identity column gets incremented, i dont want any of the values to be skipped due to an error as that number has to be accounted for.

do you know anyway in which this is possible to rollback the identity value from the DB?nihar,

I don't know your stored procedure processing fully, but I think using DBCC CHECKIDENT() will help you out.

Depending on what the current identity value is for the table in question in relation to any record gaps, you may want to examine IDENT_CURRENT() too.

Check BOL for details...

Hope this helps!

Kael|||thanks Kael,

that worked fine. dbcc checkident

heres what i was doing

.
.
begin transaction
insert into sometab values (somevalues)
set @.outparam = @.@.identity

insert into someother tab values (@.outparam...)
if @.@.error <> 0
begin
rollback transaction
dbcc checkident('sometab') --this is what i have added now
end
else
begin
commit transaction
end

this i have done as due to rollback the identity should not have increased.

thanks again.|||Hmmm...that's funny. I'm trying to image by looking at your code how the identity would increase, even though the transaction is being rolled back, but I can visualize it. Oh well. Looks like that DBCC command worked for you, so I'd quit while I'm ahead!

Kael|||hey it increase the value as soon as i insert into the table.

try this:
begin trans
insert into a table with identity column
select @.@.identity
rollback
insert again
check identity column it will have increased skipping the one which rolled back.

if u want i can give u the entire stored proc attached: its 200 odd lines :D|||Identity is designed for multitasking.
If you insert values 1-3, others can insert 4-5. If you rollback and they don't, they must have values 4-5. So you must write your own multitasking code for values without gaps.

Good luck!|||If that is the case then what is the purpose of identity.

Can you tell me what will happen if i insert values 1-3 then rollback, others insert 4-5 and save.
after rollback i call the 'dbcc checkident' proc. what happens then? Is it correct to do that?

if not proper what alternatives do I have to consider?|||You can use dbcc checkident reseed, but only if you are ADMISTRATOR.
No user can use it even by trigger. I recomend something like SP with

begin tran
INSERT TABLEX(XID)
select max(XID)+1
from TABLEX (XLOCK)
.
.
.
if ...
ROLLBACK
else
COMMIT

I am not sure about the level of locking used. I cannot use BOL now.

Good luck!|||Tell me what happens when u lock the insert max, and someone else call the max of whatever.. if u get 3, he will also get 3 since u havent committed yet.

in this case what happens, you have to trap a primary key violation and call insert again?

u will have to lock the table in that case..

or what else?|||He must wait, the same for dbcc checkident.|||it Wouldnt be ideal as conflicts will also arise when someone is editing the table.

where time would be the essence this wont really work. i have seen it happen.. even though the lock is for the minimal of time, any procedure which has to wait for another to release isnt the ideal construct.|||A. 1, 2, 3 -> one user or else high locking
B. 1, 7, 45 -> an identity and no special problems

You must decide. Sometimes you must choose A :)

IDENTITY column problems...

Hi, I have a problem with my IDENTITY column (item_id) and my INSERT statement into my 'inventory' table.

The stored procedure is executed from VB6, if the user enters an error, and the INSERT statement does not complete the IDENTITY column still increments. i.e. The 'amount' field is NOT NULL, if the user forgets to enter an amount, an error occurs in VB and the row is not inserted, but the IDENTITY column still increments.

I am NOT worried about filling in IDENTITY column gaps if a record is deleted, but I do want IDENTITY values to be in sequence (NO GAPS) when inserting records.

I have looked at DBCC CHECKIDENT, but dont understand how to use the values returned from it.

Here is the SP I am using:

CREATE PROCEDURE insert_inventory
@.item_id int,
@.item_name varchar(20),
@.description varchar(100),
@.notes varchar(255),
@.amount char(8)
AS
SET NOCOUNT ON
DECLARE @.transaction_date datetime

BEGIN TRANSACTION

IF (@.item_name = '') SET @.item_name = NULL
IF (@.description = '') SET @.description = NULL
IF (@.notes = '') SET @.notes = NULL
IF (@.amount = '') SET @.amount = NULL

SET @.transaction_date = GETDATE()
INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)
IF @.@.ROWCOUNT = 0 OR @.@.ERROR <> 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
SET @.item_id = @.@.IDENTITY
INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))
IF @.@.ROWCOUNT = 0 OR @.@.ERROR != 0
BEGIN
RAISERROR('insert_inventory SP FAILED', 16, 1)
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTIONI think u have to make a sequence table with which u can control the increment of the sequence number.

Examples:

1 Sequnce Table

CREATE TABLE [SEQUENCES] (
[seq_name] [varchar] (255) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[seq_start] [int] NOT NULL ,
[seq_step] [int] NOT NULL ,
[seq_curval] [int] NOT NULL ,
[maxvalue] [int] NOT NULL ,
[ifcycle] [bit] NOT NULL ,
[remark] [nvarchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL CONSTRAINT [DF_SEQUENCES_remark] DEFAULT (''),
[Status] [int] NOT NULL CONSTRAINT [DF_SEQUENCES_Status] DEFAULT (0),
CONSTRAINT [PK__SEQUENCE__76CBA758] PRIMARY KEY CLUSTERED
(
[seq_name]
) ON [PRIMARY]
) ON [PRIMARY]
GO

2 The sp get the incremetal sequence number

CREATE PROCEDURE dbo.sp_GetSequenceNo
(@.SequenceName varchar(255),@.seqno int output)
AS
BEGIN
set nocount on
BEGIN TRAN
select @.seqno=0
UPDATE dbo.Sequences SET seq_curval=seq_curval+seq_step
WHERE seq_name=@.SequenceName
IF @.@.error!=0
BEGIN
ROLLBACK tran
return 0
END
SELECT @.seqno=seq_curval FROM dbo.Sequence
WHERE seq_name=@.SequenceName
COMMIT TRAN

select @.seqno

set nocount off
END

3 make a function which call the sp in step 2

create function dbo.fn_default_seqno(@.SequenceName varchar(255))
RETURNS int
AS
BEGIN
declare @.Seqno int

EXEC dbo.sp_GetSequenceNo @.SequenceName,@.Seqno output

RETURN @.Seqno

END

4 u can set the function as the defaut value of your table's id column|||i don't have a solution, but i would like to ask a question
I do want IDENTITY values to be in sequence (NO GAPS) when inserting recordsi'm very curious: why?

what are you doing that depends on no gaps? counting records by subtracting first id number assigned from last id number assigned?

i've seen this problem many times, and i'm always interested in what people try to get identity columns to do for them

rudy
http://r937.com/|||I just dont want wasted records if there is no need. With my problem, if a user, when inserting one record happened to enter invalid values 10 times, then there would be 10 wasted (non retrievable) records in the system.|||if the inserted records have invalid values and in fact did not get inserted, then they don't exists, right?

so the only thing "wasted" are numbers that did not get assigned

you could just as easily worry about the ten numbers between 423475345 and 423475355 -- those didn't get assigned, either

;)|||thats the problem, they DONT get inserted, but IDENTITY value still increments

ID values are shown to user, given to clients as Customer ID's, so would look better to be as close together as possible, I think anyway|||ID values should not be shown to user, that's not what they are for

if you must do so, consider using random numbers, not sequential

do you do anything to ensure that a user can access only her own data via the ID?

and how would a user know that the ID number after his number isn't really there? and why would he care?

sorry to be so persistent, i'm just curious

rudy|||Yeah u r right, random would be better, so...

How would I go about generating a RANDOM UNIQUE five (could be more or less, but 5 would be best) digit number for use as a primary key for my database table?

I want to end up with keys looking like this, I can append the 3 char code to the number:

Clients table:

CLT45245
CLT27441
etc

Volunteers table

VOL26734
VOL29063
etc|||Just to clarify, the only user of the system is the owner of the system, the clients and volunteers mentioned would never see the DB, they are only stored on it, but they would be sent there client/volunter ID once entered into it.|||see RAND (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp)

since you want only 5 digits, you might have to generate a number, attempt the insert, and if you hit a dupe, generate another, and attempt the insert again

a stored proc would be best for this

rudy|||SET @.RANDOM = ((99999 - 10000) * Rand() + 10000)

Thats my 5 digit random number, but how would I implement this using a SP?

Identity column exists for a table - how to know programatically

Hi,
Is there any way, that I can determine whether a table has any identity
column, programmatically in SQL Server. In other words, is it stored some
where like syscolumns or sysconstraints or whatever, whether a table has
identity column and what column has the identity property set to on? I am
referring to SQL Server 2000.
Thanks in advance
oursptHi
SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
IDENT_INCR(OBJECT_NAME(id)) AS incr,
OBJECT_NAME(id) AS tbl
FROM syscolumns
WHERE (status & 128) = 128
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||Hi,
check for sp_help <table_name> in BOL
http://msdn.microsoft.com/library/d... />
p_304w.asp
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||You can use the OBJECTPROPERTY() function for that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ourspt" <ourspt@.discussions.microsoft.com> wrote in message
news:D9DFCDEE-B7D6-4E3C-A40F-D352C1DF4308@.microsoft.com...
> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt|||If what you're trying to do is determine which column it is, then You can
refer to it direstly in a Select Statement using the keyword IDENTITYCOL, as
in
Select IDENTITYCOL From TableName
If there is no IdentityColumn in the table this will, howver, throw an
error...
***Invalid column name 'identitycol'.***
"ourspt" wrote:

> Hi,
> Is there any way, that I can determine whether a table has any identity
> column, programmatically in SQL Server. In other words, is it stored some
> where like syscolumns or sysconstraints or whatever, whether a table has
> identity column and what column has the identity property set to on? I am
> referring to SQL Server 2000.
> Thanks in advance
> ourspt

Identity

Suppose we have a table with a column which has a property Is Identity set to true.

Is any programmatic way ( for example, in stored procedure ) to change this property to false?

Hi,

have a look at this article:

http://www.eggheadcafe.com/community/aspnet/9/10005322/remove-the-identity-prope.aspx

--
SvenC