Save or Commit to a table.

I'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.I'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.
:D :p LOL Good joke man,Hey guys check this post and ofcourse the man's profile who posted it...|||Is this like a cultural thing? I don't tell jokes in this forum. Thats what the yak corral is for. General conversation. I guess that's why I turn webpro over there. IE (India's Microsoft) any way, If you can't help save the sarcasm.|||Is this like a cultural thing? I don't tell jokes in this forum. Thats what the yak corral is for. General conversation. I guess that's why I turn webpro over there. IE (India's Microsoft) any way, If you can't help save the sarcasm.
Please help me to understand your question...I didn't meant to be the YAk Corral way...anyway ,Do you want to insert data into table or update data in an existing table?That you can easily do by insert or update statements in procedures.You can use most Transact-SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) must be the first (or only) statement in a command batch, and therefore aren't allowed in stored procedures.
Well,When I checked your profile I found you to be a DBA thats way...|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SWR_save_Work_Orde]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:
---------------------------

(
@.WO_PK int, -- Work Order’s Primary Key.
@.WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
@.WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
@.WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
@.WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
@.WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
@.RTN_Code int OUTPUT
)

AS

BEGIN TRAN

saveTE f_WorkOrder
SET WO_EST_START = @.WO_EST_START,
WO_EST_COMP = @.WO_EST_COMP,
WO_BOOLEAN_1 = @.WO_BOOLEAN_1,
WO_BOOLEAN_2 = @.WO_BOOLEAN_2,
WO_MOD_DATE = @.WO_MOD_DATE
WHERE WO_PK = @.WO_PK

IF @.@.ERROR != 0 GOTO ERR_HANDLER

COMMIT TRAN

SET @.RTN_Code = 0
RETURN

ERR_HANDLER:
ROLLBACK TRAN

SET @.RTN_Code = -20001
RETURN

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Sorry, This is what I have so far|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SWR_save_Work_Orde]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:
---------------------------

(
@.WO_PK int, -- Work Orders Primary Key.
@.WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
@.WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
@.WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
@.WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
@.WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
@.RTN_Code int OUTPUT
)

AS

BEGIN TRAN

saveTE f_WorkOrder
SET WO_EST_START = @.WO_EST_START,
WO_EST_COMP = @.WO_EST_COMP,
WO_BOOLEAN_1 = @.WO_BOOLEAN_1,
WO_BOOLEAN_2 = @.WO_BOOLEAN_2,
WO_MOD_DATE = @.WO_MOD_DATE
WHERE WO_PK = @.WO_PK

IF @.@.ERROR != 0 GOTO ERR_HANDLER

COMMIT TRAN

SET @.RTN_Code = 0
RETURN

ERR_HANDLER:
ROLLBACK TRAN

SET @.RTN_Code = -20001
RETURN

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

1. Do you want to insert data in a new table?
2.or do you want to update data in a existing table?PLz specify that
The red marked areas are the regions where you should do your rectifications...|||if you want to update an existing data in an existing table try this..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SWR_save_Work_Orde]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:
---------------------------

(
@.WO_PK int, -- Work Order’s Primary Key.
@.WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
@.WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
@.WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
@.WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
@.WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
@.RTN_Code int OUTPUT
)

AS

BEGIN TRAN

Update TE_f_WorkOrder
SET WO_EST_START = @.WO_EST_START,
WO_EST_COMP = @.WO_EST_COMP,
WO_BOOLEAN_1 = @.WO_BOOLEAN_1,
WO_BOOLEAN_2 = @.WO_BOOLEAN_2,
WO_MOD_DATE = @.WO_MOD_DATE
WHERE WO_PK = @.WO_PK

IF @.@.ERROR != 0 GOTO ERR_HANDLER

COMMIT TRAN

SET @.RTN_Code = 0
RETURN

ERR_HANDLER:
ROLLBACK TRAN

SET @.RTN_Code = -20001
RETURN

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||After revising the code I still get this error message.

Server: Msg 170, Level 15, State 1, Procedure SWR_Save_Work_Orde, Line 10
Line 10: Incorrect syntax near 'SWR_Save_Work_Orde'.
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error|||It looks like you may have to comment out the

Possible values = 1, 2, 3, 4, 5.
1 = Routine,
2 = Low,
3 = Medium,
4 = High,
5 = Emergency.

As well as the descriptions of the other variables. I take it you inheirited this?|||Rudra: I hardly find Gary's interests at all questionable. And his statement that his T-SQL is "rusty" does imply that he is not joking. Perhaps his PL/SQL is considerably less rusty. It is good to see that you helped him out in the end, even if the welcome was...well...less than welcoming.|||The whole term "DBA" is misleading, because it can refer to two distinct disciplines:
DataBase Administrator: Proficient in installing, securing, and optimizing servers.
DataBase Architect: Proficient in schematic design and SQL coding.|||After revising the code I still get this error message.

Server: Msg 170, Level 15, State 1, Procedure SWR_Save_Work_Orde, Line 10
Line 10: Incorrect syntax near 'SWR_Save_Work_Orde'.
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error

I didn't gave that code,it seems that you havn't gave a single look at my rectified code.Thats good...carry one|||Rudra: It is good to see that you helped him out in the end, even if the welcome was...well...less than welcoming.

I always try to be a gentleman,just like PatP...Batman...hmm Rudy.. you and the rest of great ones.I know my DBA expectation is quite high,I mean when is see somebody as DBA,I don't expect something from them that I already know. Garrydawkins is self sufficient , as you see he hasn't even used my code..;)|||rudra, you are freaking people out.

Please stop.|||Gary, I took a look at your attached code. The problem is in this section:CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:
As you can see, you are issuing a CREATE PROCEDURE statement with no code body, and then issuing the GO command terminator. Then you issue the same CREATE PROCEDURE header a second time. It looks like a copy-paste error to me.|||Ladies & Gentlement, Sorry for the delay in response. Building access was limited over the weekend and that's were the code was. This mods work fine and I do appreciate and thank you for all your help.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SWR_save_Work_Orde]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_Save_Work_Orde
-- DATE CREATED: 2006-04-06
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: saves associated with f_WorkOrders.
-- DEPENDENCIES: SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE dbo.SWR_save_Work_Orde
---------------------------
-- AUTHOR: gdawkins
-- PROCEDURE: SWR_save_Work_Orde
-- DATE CREATED: 2006-04-07
-- DATE MODIFIED:
-- DATE REMOVED:
-- DESCRIPTION: savetes fields associated with f_WorkOrders.
-- DEPENDENCIES:
---------------------------

(
@.WO_PK int, -- Work Orders Primary Key.
@.WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
@.WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
@.WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
@.WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
@.WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
@.RTN_Code int OUTPUT
)

AS

BEGIN TRAN

Update TE_f_WorkOrder
SET WO_EST_START = @.WO_EST_START,
WO_EST_COMP = @.WO_EST_COMP,
WO_BOOLEAN_1 = @.WO_BOOLEAN_1,
WO_BOOLEAN_2 = @.WO_BOOLEAN_2,
WO_MOD_DATE = @.WO_MOD_DATE
WHERE WO_PK = @.WO_PK

IF @.@.ERROR != 0 GOTO ERR_HANDLER

COMMIT TRAN

SET @.RTN_Code = 0
RETURN

ERR_HANDLER:
ROLLBACK TRAN

SET @.RTN_Code = -20001
RETURN

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Good to see the rectified one worked for you. :)|||Hey Gang, I got one for you.

Errror message:

Server: Msg 137, Level 15, State 2, Line 19
Must declare the variable '@.EFFECTIVE_DATE'.

Code to get error;
SELECT LOG_PK,
LOG_REQUESTOR,
LOG_REQ_PHONE,
LOG_CHAR_60_1,
LOG_CHAR_30_4,
LOG_CN_FK,
LOG_CREATE_DATE,
LOG_REQUEST,
(LOG_STATUS),
(LOG_TYPE),
LOG_FU_FK,
LOG_FB_PK,
LOG_LT_FK,
LOG_CREATE_DATE
FROM f_Requests
WHERE LOG_RQ_FK IS NOT NULL AND
LOG_LT_FK <> 10174 AND
LOG_RC_CODE = S
AND (LOG_MOD_DATE > @.EFFECTIVE_DATE OR
LOG_CREATE_DATE > @.EFFECTIVE_DATE)|||So, declare @.EFFECTIVE_DATE.

In TSQL, all variables must be explicitly declared before being referenced, either in a declaration section or as a parameter to the procedure/function.