generate & compare id from table in sql storeprocedure

[premprakashbhati]

hello sir,
goodevening....
iam working on vb6.0 and sql2005.

 "select max(centreid) from regionmaster where centreid like '" & intBranchPrefix & "%'", DBConnection, adOpenKeyset, adLockOptimistic
         If rsMain.RecordCount > 0 Then
            intID = Mid(rsMain(0), 3, 13)
        End If
         intCentreID = intBranchPrefix & (intID + 1)
 

i have to convert the above code command in sql storeprocedure
where i have to generate centreid with branchprefix(ex :99 or 11) which is supplied by the application.i.e , intBranchPrefix. i have to take that branchprefix in storeprocedure and generate the centre id....that is
centerid=branch prefix & (id+!)
plz help me ......
prem........

[Delerna]

create proc GenerateCentreID @intBranchPrefix int
as
declare @MaxCentreID int,@intID varchar(50)
set @MaxCentreID=(select convert(int,max(substring(centreid,3,13)) 
                  from regionmaster 
                  where centreid like @intBranchPrefix%
                  )
 
set @intID=@intBranchPrefix*10000000000000 + @MaxCentreID+1
go
 

I think thats right, you might need to fix some syntax. I haven't tested it

[premprakashbhati]

Mr.Delerna..Goo dMorning..,
i have got what u wrote but when i executed the proceure ..the error cameIncorrect syntax near ) of select statement

[SnehaAgrawal]

Hi the proc is correct just one closing bracket is missing
(int,max(substr ing(centreid,3, 13)))
I hope u got the ans.

[Delerna]

what is the data type of the field centreid in table regionmaster?
I will mock up a table so I can test the code.
I am guessing it is string but I don't want to guess

[Delerna]

if it is a character type then

create proc GenerateCentreID @intBranchPrefix varchar(20)
as 
declare @MaxCentreID bigint,@intID varchar(20) 
set @MaxCentreID=(select convert(bigint,max(substring(centreid,3,13))) 
                  from regionmaster 
                  where centreid like @intBranchPrefix+'%'
                  ) 
 
set @intID=@intBranchPrefix + convert(varchar(13),@MaxCentreID+1)
select @intID
 
go 
 

the error you mention was due to there not being enough closing brackets in the select criteria of the query.
Also the parameter was not the correct type.
Also I forgot the + '%' in the where

[premprakashbhati]

NOTE:datatype of centerid is decimal(13,2) in table...
here came the error when i execute the given proc

Argument data type decimal is invalid for argument 1 of substring function.

[Delerna]

Then try converting centreid to character in the query.
The like opperator works with strings!

create proc GenerateCentreID @intBranchPrefix varchar(20) 
as  
declare @MaxCentreID bigint,@intID varchar(20)  
set @MaxCentreID=(select convert(bigint,max(substring(convert(varchar(20),centreid),3,13)))  
                  from regionmaster  
                  where convert(varchar(20),centreid) like @intBranchPrefix+'%' 
                  )  
 
set @intID=@intBranchPrefix + convert(varchar(20),@MaxCentreID+1) 
select @intID 
 
go  
 

[premprakashbhati]

thanks Delerna...i got it..

[premprakashbhati]

hi delerna ...sorry for troubling u again...
when iam calling the store procedure from vb6 application in CmdSave_Click
it is giving Error:The Precision is invalid ...
iam not getting the exact thing/meaning...
so plz help me out...
here is the code:

Private Sub SaveBranchProcedure()
 
Dim cmd  As New ADODB.Command
Dim param1 As ADODB.Parameter
Dim pm As Integer
Dim i As Integer
 
Set cmd.ActiveConnection = DBConnection
 
     cmd.CommandType = adCmdStoredProc
'     cmd.CommandText = "sp_savebranchmaster"
       cmd.CommandText = "sp_branchonly"
     cmd.Parameters.Append cmd.CreateParameter("BranchPrefix", adNumeric, adParamInput, 13, intBranchPrefix)
'    cmd.Parameters.Append cmd.CreateParameter("centreid", adNumeric, adParamOutput, intID)
    cmd.Parameters.Append cmd.CreateParameter("centretype", adVarChar, adParamInput, 3, "SR")
    cmd.Parameters.Append cmd.CreateParameter("centrename", adVarChar, adParamInput, 60, Val(TxtName))
    cmd.Parameters.Append cmd.CreateParameter("code", adVarChar, adParamInput, 6, Val(TxtCode))
 
    If CmbBusinessType.Text = "Booking" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BOOK")
 
    ElseIf CmbBusinessType.Text = "Delivery" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "DELI")
 
    ElseIf CmbBusinessType.Text = "OTHER" Then '"Booking and Delivery"
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "BKDL")
 
    ElseIf CmbBusinessType.Text = "Transhipment" Then
        cmd.Parameters.Append cmd.CreateParameter("BRANCH_BUSINESS_TYPE", adVarChar, adParamInput, 6, "TRAN")
    End If
 
    cmd.Parameters.Append cmd.CreateParameter("levelno", adNumeric, adParamInput, 13, 0)
    cmd.Parameters.Append cmd.CreateParameter("indexno", adNumeric, adParamInput, 13, 0)
    cmd.Parameters.Append cmd.CreateParameter("groupno", adNumeric, adParamInput, 13, 0)
 
     cmd.Parameters.Append cmd.CreateParameter("group_node_flag", adVarChar, adParamInput, 1, "A")
 
     If Trim(TxtAddress) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address", adVarChar, adParamInput, 50, Val(TxtAddress))
    End If
 
     If Trim(txtAdd1) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address1", adVarChar, adParamInput, 50, Val(txtAdd1))
    End If
 
     If Trim(txtAdd2) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("address2", adVarChar, adParamInput, 50, Val(txtAdd2))
    End If
 
       If Trim(TxtPhone) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("phone", adVarChar, adParamInput, 20, Val(TxtPhone))
    End If
 
    If Trim(TxtFax) = "" Then
        cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 4, Null)
     Else
        cmd.Parameters.Append cmd.CreateParameter("fax", adVarChar, adParamInput, 20, Val(TxtFax))
    End If
 
    If Trim(TxtEmail) = "" Then
      cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("email", adVarChar, adParamInput, 30, Val(TxtEmail))
   End If
 
    If Trim(TxtManager) = "" Then
     cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 4, Null)
    Else
     cmd.Parameters.Append cmd.CreateParameter("manager", adVarChar, adParamInput, 30, Val(TxtManager))
    End If
 
    If Trim(TxtMgrPhone) = "" Then
       cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("MANAGER_PHONE", adVarChar, adParamInput, 20, Val(TxtMgrPhone))
    End If
 
    If Trim(TxtRemarks) = "" Then
      cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 4, Null)
    Else
      cmd.Parameters.Append cmd.CreateParameter("REMARKS", adVarChar, adParamInput, 50, Val(TxtRemarks))
    End If
 
    cmd.Parameters.Append cmd.CreateParameter("mainparent", adNumeric, adParamInput, 13, 0)  'frmTreeMaster.sngMainParentKey
 
 
    If ChkAcct.Value = 1 Then
      cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "Y")
    ElseIf ChkAcct.Value = 0 Then
      cmd.Parameters.Append cmd.CreateParameter("acct_y_n", adVarChar, adParamInput, 1, "N")
    End If
 
    cmd.Parameters.Append cmd.CreateParameter("acct_branch", adVarChar, adParamInput, 20, vsgBranch.Text)
 
     cmd.Parameters.Append cmd.CreateParameter("CREATED_BY", adNumeric, adParamInput, 13, g_UserData.UserID)
    cmd.Parameters.Append cmd.CreateParameter("CREATED_DATE", adDate, adParamInput, 13, Format(g_LoginData.ServerDate, "DD-MMM-YY"))
    cmd.Parameters.Append cmd.CreateParameter("delete_flag", adVarChar, adParamInput, 1, "N")
 
cmd.Execute
 
 
End Sub