Insert Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert Stored Procedure

Author

Topic

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 00:45:50

Hi All,
I have a table and fields like and data should be insert like the format below
APPSER DOCID
1 1
2 1
3 2
1 2
2 3
3 1
i want to write a insert stored procedure so that user just enter the DOCID and APPSER should come in the above format automatically by stored procedure..i need your help my friends please.
Thanks & Good Day

Sachin.Nand

2937 Posts

Posted - 2011-01-26 : 01:05:12

So is the user going to insert enter multiple DOCID ?

PBUH

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 01:09:21

Hi
Yes user can enter multiple docid.

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-26 : 01:22:31

But on what logic should those APPSER values be generated ?

PBUH

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 01:26:07

Hi Sachin,
Thanks for your reply.
i will change the structure and the table should contain like this.
APPSER DOCID
1 1
2 1
3 1
1 2
2 2
3 2
1 3
2 3
3 3
i hope you understand this..please i need your help.
thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-26 : 01:56:32

One last question.In what format will the DOCID be entered ?

PBUH

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 01:59:24

Hi Sachin,

The DOCID and APPSER datatype is numeric.

Thanks.

Sachin.Nand

2937 Posts

Posted - 2011-01-26 : 02:29:34

What I meant was the "format"not the datatype


declare @t table(DOCID int)

insert @t 
select  1 union all
select  1 union all
select  1 union all
select  2 union all
select  2 union all
select  2 union all
select  3 union all
select  3 union all
select  3 

select DOCID,ROW_NUMBER()over(partition by DOCID order by DOCID)APPSER  from @t

PBUH

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 02:38:05

Hi sachin,

actually i need insert SP where user can pass docid as parameter and the appser should follow the below format.

Thanks

Sachin.Nand

2937 Posts

Posted - 2011-01-26 : 02:46:20

See you are giving information in pieces which aren't even complete.

I asked you the format of the docid which will go as a parameter to the SP.

PBUH

rizwanmgm
Starting Member

8 Posts

Posted - 2011-01-26 : 02:59:16

dear sachin..this is the complete structure of the table.can you understand with this.
USE [IMPATCHI]
GO

/****** Object: Table [dbo].[_ORGDOCAPP] Script Date: 01/26/2011 10:54:31 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[_ORGDOCAPP](
[APPSER] [numeric](18, 0) NOT NULL,
[DOCID] [numeric](18, 0) NOT NULL,
[WFOTASKID] [numeric](18, 0) NOT NULL,
[APPROW] [numeric](18, 0) NOT NULL,
[USERID] [nvarchar](3) NOT NULL,
[Active] [bit] NULL,
[USRID] [nvarchar](3) NULL,
[ENTDATE] [datetime] NOT NULL,
[CredAppAuthority] [tinyint] NULL,
CONSTRAINT [PK_ORGDOCAPP] PRIMARY KEY CLUSTERED
(
[DOCID] ASC,
[WFOTASKID] ASC,
[APPROW] ASC,
[USERID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_ORGDOCAPP] UNIQUE NONCLUSTERED
(
[DOCID] ASC,
[APPROW] ASC,
[USERID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[_ORGDOCAPP] ADD CONSTRAINT [DF_ORGDOCAPP_TASKID] DEFAULT ((1)) FOR [WFOTASKID]
GO

ALTER TABLE [dbo].[_ORGDOCAPP] ADD CONSTRAINT [DF_ORGDOCAPP_ENTOPR] DEFAULT ((0)) FOR [Active]
GO

ALTER TABLE [dbo].[_ORGDOCAPP] ADD CONSTRAINT [DF_ORGDOCAPP_ENTDATE] DEFAULT (getdate()) FOR [ENTDATE]
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources