how to concatenate in stored procedure

Question

0.00/5 (No votes)

See more:

, +

how to concatenate in stored procedure

I want to save P101 as id in One table and auto increment only number like P101,P102,P103 with every insertion of data this p101 should auto increment

this is my procedure

i want to save P101, P102 in Monitor_ID and Qty_Device_ID columns

SQL

create Procedure [dbo].[pro_Monitor]
(

  @Monitor_ID varchar(50),
  @Monitor_Name varchar(50),
  @Monitor_model varchar(50),
  @Monitor_O_N int ,
  @Qty_Device_ID varchar,
  @Qty_Recived bigint
  

)

As
Begin
insert into dbo.monitor (Monitor_ID ,Monitor_Name,Monitor_date,Monitor_model,Monitor_O_N )
values (@Monitor_ID ,@Monitor_Name,getdate(),@Monitor_model,@Monitor_O_N);

insert into dbo.Tbl_ReceivedQty(Qty_Device_ID,Qty_Received)
values (@Qty_Device_ID,@Qty_Recived);
end

Posted 21-May-13 0:16am

$ultaNn

Add a Solution

Comments

gvprabu 21-May-13 6:27am

U need to do this in backend or front end

$ultaNn 21-May-13 6:31am

i have to insert monitors one by one in back end P101 P102 and so on should save in db table with every insertion on new monitor.
Ex : If i enter Name: Dell Model: Inspiron Quantity :100 then in Monitor ID it should save P101 when i enter second model It should save P102

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad Khandekar · Answer 1 · 2013-05-21T00:28:00

Hello,

Sql Server 2012 has introduced SEQUENCE objects, which allow you to generate sequential numeric values not associated with any table. You can define a sequence as shown below.

SQL

CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1

So lets say you have defined a sequence named "MonitorSeq" then you can use following syntax in your stored procedure to get the next value.

SQL

DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Schema.MoniterSeq;

Please don't forget to change Schema to your actual Schema Name

Regards,

gvprabu · Answer 2 · 2013-05-21T00:29:00

Solution 2

hi,

Try like this...

SQL

create Procedure [dbo].[pro_Monitor]
(
  @Monitor_Name varchar(50),
  @Monitor_model varchar(50),
  @Monitor_O_N int ,
  @Qty_Device_ID varchar,
  @Qty_Recived bigint

 )
 
As
Begin
DECLARE @Monitor_ID varchar(50)=NULL
-- Get the next Monitor count 
SELECT @Monitor_ID ='P'+CAST(NextValue AS VARCHAR(10)) FROM sysPeram WHERE Code='Monitor'
SELECT @Qty_Device_ID=@Monitor_ID 

insert into dbo.monitor (Monitor_ID ,Monitor_Name,Monitor_date,Monitor_model,Monitor_O_N )
values (@Monitor_ID ,@Monitor_Name,getdate(),@Monitor_model,@Monitor_O_N);
 
insert into dbo.Tbl_ReceivedQty(Qty_Device_ID,Qty_Received)
values (@Qty_Device_ID,@Qty_Recived);

-- Update System Peram table 
UPDATE sysPeram SET NextValue=NextValue+1 WHERE Code='Monitor'
end

GVPrabu

Posted 21-May-13 0:29am

gvprabu

Updated 21-May-13 1:53am

v3

Comments

$ultaNn 21-May-13 7:40am

I am getting this error
Procedure or function 'pro_Monitor' expects parameter '@Monitor_ID', which was not supplied.

con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "pro_Monitor";
cmd.Parameters.AddWithValue("@Monitor_Name", TextBox1.Text);
cmd.Parameters.AddWithValue("Qty_Received", Quantity.Text);
cmd.Parameters.AddWithValue("@Monitor_O_N", DropDownList1.Text);
cmd.Parameters.AddWithValue("@Monitor_user", Label1.Text = Session["l_name"].ToString());
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();

gvprabu 21-May-13 7:50am

yes, so you have to declare that Parameter in that SP as NULL....
I updated the solution... check now

gvprabu 21-May-13 7:54am

I removed that Parameter in SP and added as local variable inside the SP. Check now

$ultaNn 22-May-13 1:37am

i checked i am getting this error
Invalid object name 'sysPeram'.
in c#code.

gvprabu 22-May-13 3:44am

Hi Friend... U need to create a new table for Keep all Running Number in this.

gvprabu 22-May-13 3:47am

This table is like as follows
CREATE TABLE sysPeram(Code VARCHAR(10), NextValue INT)
and Insert one Record for "Monitor" like INSERT INTO sysPeram(Code, NextValue) VALUES('Monitor',1)