none
master ms sql server 2008 come here please

    Question

  • here my query

    create database tugasbdt
    go
    use tugasbdt
    go
    create table login
    (
    npm varchar (25),
    nama varchar (25)
    )
    go
    
    create proc daftar
    @nama varchar(25)
    as
    begin
    	if not exists(select npm from login)
    	begin
    		insert into login values((year(getdate())*1000000)+250001,@nama)
    	end
    	else
    	begin
    		declare @npm int
    		select top 1 @npm=right(npm,4)+1 from login order by npm desc
    		insert into login values((year(getdate())*1000000)+250000+@npm,@nama)
    	end
    	select * from login
    end

    so if i

    exec daftar 'muhammad aprizal'

    exec daftar 'Leonidas'

    exec daftar 'Ohz'

    but i dont know query if i

    exec npm,jurusan

    example :

    exec 'ijal','ti'

    exec 'reza','ti'

    exec 'stef','si'

    exec 'john','ka'

    exec 'bolt','si'

    exec 'mest','ka'

    exec 'lahar','ti'

    so the result

    anybody know this query ?

    im use ms sql server 2008

    pls help me

    Wednesday, May 15, 2013 1:20 AM

All replies

  • Use the Split function to separate the input comma separated and use the first half in the query.

    google for the function details or check http://www.codeproject.com/Tips/85943/Split-function

    Also increase the procedure input date type length accordingly.


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 15, 2013 1:44 AM
  • I dont know I understood your requirement clearly.

    But you want to return an extra column from proc. But nothing to do with insert. If am correct, then you need to change your proc as below:

    create proc daftar
    @nama varchar(25),@jurusan varchar(100)
    as
    begin
    if not exists(select npm from login)
    begin
    insert into login values((year(getdate())*1000000)+250001,@nama)
    end
    else
    begin
    declare @npm int
    select top 1 @npm=right(npm,4)+1 from login order by npm desc
    insert into login values((year(getdate())*1000000)+250000+@npm,@nama)
    end
    select *,@jurusan from login
    end


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 15, 2013 1:49 AM
  • As you want to view the final output in 3 column, i am modifying your insert statement and adding 1 more parameter.

    create proc daftar
    @nama varchar(25),@jurusan varchar(100)
    as
    begin
    if not exists(select npm from login)
    begin
    insert into login values((year(getdate())*1000000)+250001,@nama)
    end
    else
    begin
    declare @npm int
    select top 1 @npm=right(npm,4)+1 from login order by npm desc
    insert into login values((year(getdate())*1000000)+250000+@npm,@nama,@jurusan)
    end
    end


    Regards, RSingh

    Wednesday, May 15, 2013 3:23 AM
  • sorry for that...

    what i mean is when insert into login (nama,jurusan) values ('ijal','ti') so npm automatic filled with it self like exec daftar 'ijal','ti' so result at npm is 201325001

    and if im exec daftar 'reza','ti' the middle npm still '25' and end of npm change to +1,like 201325002.

    like picture number 2

    but if i exec daftar 'stef','si' the middle of npm change '25' to '24' and end of npm back to start '1' like '201324001'

    and if exec daftar 'john','ka' the middle of npm change again to '21' from '24' and end of npm back to start again '1' like '201321001'

    and if im exec that procudure with same jurusan,its added own suit ...

    ex :

    like this table

    see that npm,always change if jurusan same and different

    middle npm

    'ti'=25

    'si'=24

    'ka'=21

    if i added again exec daftar 'gago','ti'

    and new result is

    npm                             nama            jurusan

    2011250004               gago               'ti'

    and so on...

    sorry for bad explain T.T

    Wednesday, May 15, 2013 8:21 AM
  • Try the below: (Not tested)

    create table login
    (
    npm varchar (25),
    nama varchar (25),
    jurusan Varchar(100)
    )
    go
    create proc daftar
    @nama varchar(25),@jurusan varchar(100)
    as
    begin
    	if not exists(select npm from login)
    	begin
    		insert into login values((year(getdate())*1000000)+250001,@nama)
    	end
    	else
    	begin
    		declare @npm int
    		select top 1 @npm=right(npm,4)+1 from login where jurusan = @jurusan order by npm desc
    		insert into login values((year(getdate())*1000000)+250000+@npm,@nama,@jurusan)
    	end
    	select * from login
    end


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, May 15, 2013 8:44 AM
  • up up maybe other have another answer
    Thursday, May 16, 2013 6:16 AM