Showing posts with label save. Show all posts
Showing posts with label save. Show all posts

Wednesday, March 21, 2012

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
--
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>

Identy Number

I am using an Identy number to generate a Unique ID for records.
The process is
a. Save header record (identy ID created)
b. Retrieve Identy No using a select statement via odbc
c. Dave many data recors with Identy Number as reference to the Header
Record.
How can i retrieve the Identy No with a select statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004Use the SCOPE_IDENTITY() function.
David Portas
SQL Server MVP
--|||To expand David's response... Use a select that returns the identity ie
select @.@.scope_identity
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:OHc5aqz8DHA.2308@.TK2MSFTNGP11.phx.gbl...
> I am using an Identy number to generate a Unique ID for records.
> The process is
> a. Save header record (identy ID created)
> b. Retrieve Identy No using a select statement via odbc
> c. Dave many data recors with Identy Number as reference to the Header
> Record.
> How can i retrieve the Identy No with a select statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.583 / Virus Database: 369 - Release Date: 10/02/2004
>sql

Friday, March 9, 2012

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
>