CONVERTING C# TO TSQL - 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 2005 Forums

Transact-SQL (2005)

CONVERTING C# TO TSQL

Author

Topic

kingsleen_sqlteam
Starting Member

4 Posts

Posted - 2007-10-24 : 01:28:36

this is an function i trying to change this into tsql procedure how can i do this pls hlp

set @tempId = @requestId.Substring(2, (len(requestId) - @siteLength));

Kingsleen.J

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 01:47:27

set @tempId = Substring(@requestId, 2, len(requestId) - @siteLength)

E 12°55'05.25"
N 56°04'39.16"

kingsleen_sqlteam
Starting Member

4 Posts

Posted - 2007-10-24 : 02:17:22

thank u

kingsleen_sqlteam
Starting Member

4 Posts

Posted - 2007-10-24 : 02:19:16

This is the function..

cmd.Parameters.Clear();
cmd.CommandText = "select RequestId from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId";
cmd.Parameters.Add("@SiteId", SqlDbType.Int).Value = siteID;
cmd.Parameters.Add("@siteLength", SqlDbType.Int).Value = siteLength;
Reder = cmd.ExecuteReader();

if (Reder.HasRows)
{

Reder.Read();
requestId = Reder[0].ToString();
string tempId = requestId.Substring(2, requestId.Trim().Length - siteLength).Trim();
int valint = int.Parse(tempId) + 1;
string val = valint.ToString().Trim();
int min = val.Length;
if (val.Length >= tempId.Length)
{
val = "PR" + val;
}
else
{
while (val.Length < tempId.Length)
{
val = '0' + val.Trim();
}

val = "PR" + val;
}
requestId = val + contractNo;// +"_" + Date.Year.ToString();

}
else
{

requestId = "PR00001" + contractNo;// +"_" + Date.Year.ToString();

}

Converted Sql is as follows is it correct

f ((select count(*) from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId) > 0)
Begin
Declare @valint int,@val varchar,@min int
select @requestId = RequestId from PurchaseRequest where (substring(RequestId,3,(len(RequestId)-@siteLength))) =(select max((substring(RequestId,3,(len(RequestId)-@siteLength)))) from PurchaseRequest where SiteId=@SiteId) and SiteId=@SiteId
set @tempId = Substring(@requestId, 2, len(requestId) - @siteLength)

set @valint =@tempId+1
set @val = @valint
set @min = len(val)
if (len(val) >= len(tempId))
Begin
set @val = 'PR' + @val
End
else
Begin
while (len(val) < len(tempId))
Begin
set @val = '0' + @val
End

set @val = 'PR' + @val
End
set @requestId = @val + @contractNo

End
ELSE
set @requestId = 'PR00001' + @contractNo

Kingsleen.J

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources