Increase nvarchar field value like a num

Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard

Incorrect syntax near the keyword 'OR'.

Hi,
I have a stored procedure
CREATE PROCEDURE dbo.Retrieve
(
@.SEARCH_STRING nvarchar(200),
@.COUNT int
)
AS
DECLARE @.STRING_COUNT varchar(3)
DECLARE @.SQL varchar(1000)
SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE ' +
@.SEARCH_STRING + '% )'
EXEC (@.SQL)
The stored procedure is created successfully.
But I get the error when I try to use it: (Retrieve '',10)
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OR'.
Thanks
KiranAnswered in .programming. Please don't multi-post.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Kiran" <Kiran@.nospam.net> wrote in message
news:O9oiPrX#EHA.2876@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a stored procedure
> CREATE PROCEDURE dbo.Retrieve
> (
> @.SEARCH_STRING nvarchar(200),
> @.COUNT int
> )
> AS
> DECLARE @.STRING_COUNT varchar(3)
> DECLARE @.SQL varchar(1000)
>
> SET @.STRING_COUNT = CAST(@.COUNT AS varchar(3))
> SET @.SQL='SELECT TOP ' + @.STRING_COUNT + '[ID] FROM [EMPLOYEES]
> WHERE ([NAME] LIKE ' + @.SEARCH_STRING + '% OR [EMPLOYEE_REFERENCE] LIKE '
+
> @.SEARCH_STRING + '% )'
> EXEC (@.SQL)
> The stored procedure is created successfully.
> But I get the error when I try to use it: (Retrieve '',10)
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'OR'.
>
> Thanks
> Kiran
>sql

Incorrect syntax near 'nvarchar'

When I try to insert data in sql server 2000 database with a formview. I got this error:

Line 1: Incorrect syntax near 'nvarchar'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'nvarchar'.

The insert statement is right and works manuallly.

INSERT INTO REUNIOES(DATA_P1, DATA_P2, [MIG/SIR], FRENTE, LIDER, ATIVIDADE, DATA_PLANEJADO, DATA_CONCLUSAO, DATA_REPLANEJAMENTO, STATUS, EQUIPE) VALUES (@.DATA_P1, @.DATA_P2, @.MIG, @.FRENTE, @.LIDER, @.ATIVIDADE, @.DATA_PLANEJADO, @.DATA_CONCLUSAO, @.DATA_REPLANEJAMENTO, @.STATUS, @.EQUIPE)

Please, help me with this thread.

You will probably have to show something of your other code as this is not sufficient anough to track down the error. If you have profiler, let a trace run during the manipulation within the control to see what wrong commad is executed against the SQl Server database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

The code:

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Teste.aspx.vb" Inherits="Teste" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:FormView ID="FormView1" runat="server" DataKeyNames="ID" DataSourceID="SqlDataSource1"

DefaultMode="Insert">

<EditItemTemplate>

DATA_P1:

<asp:TextBox ID="DATA_P1TextBox" runat="server" Text='<%# Bind("DATA_P1") %>'></asp:TextBox><br />

DATA_P2:

<asp:TextBox ID="DATA_P2TextBox" runat="server" Text='<%# Bind("DATA_P2") %>'></asp:TextBox><br />

MIG/SIR:

<asp:TextBox ID="MIG_SIRTextBox" runat="server" Text='<%# Bind("[MIG/SIR]") %>'></asp:TextBox><br />

FRENTE:

<asp:TextBox ID="FRENTETextBox" runat="server" Text='<%# Bind("FRENTE") %>'></asp:TextBox><br />

LIDER:

<asp:TextBox ID="LIDERTextBox" runat="server" Text='<%# Bind("LIDER") %>'></asp:TextBox><br />

ATIVIDADE:

<asp:TextBox ID="ATIVIDADETextBox" runat="server" Text='<%# Bind("ATIVIDADE") %>'></asp:TextBox><br />

DATA_PLANEJADO:

<asp:TextBox ID="DATA_PLANEJADOTextBox" runat="server" Text='<%# Bind("DATA_PLANEJADO") %>'></asp:TextBox><br />

DATA_CONCLUSAO:

<asp:TextBox ID="DATA_CONCLUSAOTextBox" runat="server" Text='<%# Bind("DATA_CONCLUSAO") %>'></asp:TextBox><br />

DATA_REPLANEJAMENTO:

<asp:TextBox ID="DATA_REPLANEJAMENTOTextBox" runat="server" Text='<%# Bind("DATA_REPLANEJAMENTO") %>'></asp:TextBox><br />

STATUS:

<asp:TextBox ID="STATUSTextBox" runat="server" Text='<%# Bind("STATUS") %>'></asp:TextBox><br />

EQUIPE:

<asp:TextBox ID="EQUIPETextBox" runat="server" Text='<%# Bind("EQUIPE") %>'></asp:TextBox><br />

ID:

<asp:Label ID="IDLabel1" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />

<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

Text="Update"></asp:LinkButton>

<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"></asp:LinkButton>

</EditItemTemplate>

<InsertItemTemplate>

DATA_P1:

<asp:TextBox ID="DATA_P1TextBox" runat="server" Text='<%# Bind("DATA_P1") %>'></asp:TextBox><br />

DATA_P2:

<asp:TextBox ID="DATA_P2TextBox" runat="server" Text='<%# Bind("DATA_P2") %>'></asp:TextBox><br />

MIG/SIR:

<asp:TextBox ID="MIG_SIRTextBox" runat="server" Text='<%# Bind("[MIG/SIR]") %>'></asp:TextBox><br />

FRENTE:

<asp:TextBox ID="FRENTETextBox" runat="server" Text='<%# Bind("FRENTE") %>'></asp:TextBox><br />

LIDER:

<asp:TextBox ID="LIDERTextBox" runat="server" Text='<%# Bind("LIDER") %>'></asp:TextBox><br />

ATIVIDADE:

<asp:TextBox ID="ATIVIDADETextBox" runat="server" Text='<%# Bind("ATIVIDADE") %>'></asp:TextBox><br />

DATA_PLANEJADO:

<asp:TextBox ID="DATA_PLANEJADOTextBox" runat="server" Text='<%# Bind("DATA_PLANEJADO") %>'></asp:TextBox><br />

DATA_CONCLUSAO:

<asp:TextBox ID="DATA_CONCLUSAOTextBox" runat="server" Text='<%# Bind("DATA_CONCLUSAO") %>'></asp:TextBox><br />

DATA_REPLANEJAMENTO:

<asp:TextBox ID="DATA_REPLANEJAMENTOTextBox" runat="server" Text='<%# Bind("DATA_REPLANEJAMENTO") %>'></asp:TextBox><br />

STATUS:

<asp:TextBox ID="STATUSTextBox" runat="server" Text='<%# Bind("STATUS") %>'></asp:TextBox><br />

EQUIPE:

<asp:TextBox ID="EQUIPETextBox" runat="server" Text='<%# Bind("EQUIPE") %>'></asp:TextBox><br />

<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"

Text="Insert"></asp:LinkButton>

<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel"></asp:LinkButton>

</InsertItemTemplate>

<ItemTemplate>

DATA_P1:

<asp:Label ID="DATA_P1Label" runat="server" Text='<%# Bind("DATA_P1") %>'></asp:Label><br />

DATA_P2:

<asp:Label ID="DATA_P2Label" runat="server" Text='<%# Bind("DATA_P2") %>'></asp:Label><br />

MIG/SIR:

<asp:Label ID="MIG_SIRLabel" runat="server" Text='<%# Bind("[MIG/SIR]") %>'></asp:Label><br />

FRENTE:

<asp:Label ID="FRENTELabel" runat="server" Text='<%# Bind("FRENTE") %>'></asp:Label><br />

LIDER:

<asp:Label ID="LIDERLabel" runat="server" Text='<%# Bind("LIDER") %>'></asp:Label><br />

ATIVIDADE:

<asp:Label ID="ATIVIDADELabel" runat="server" Text='<%# Bind("ATIVIDADE") %>'></asp:Label><br />

DATA_PLANEJADO:

<asp:Label ID="DATA_PLANEJADOLabel" runat="server" Text='<%# Bind("DATA_PLANEJADO") %>'></asp:Label><br />

DATA_CONCLUSAO:

<asp:Label ID="DATA_CONCLUSAOLabel" runat="server" Text='<%# Bind("DATA_CONCLUSAO") %>'></asp:Label><br />

DATA_REPLANEJAMENTO:

<asp:Label ID="DATA_REPLANEJAMENTOLabel" runat="server" Text='<%# Bind("DATA_REPLANEJAMENTO") %>'></asp:Label><br />

STATUS:

<asp:Label ID="STATUSLabel" runat="server" Text='<%# Bind("STATUS") %>'></asp:Label><br />

EQUIPE:

<asp:Label ID="EQUIPELabel" runat="server" Text='<%# Bind("EQUIPE") %>'></asp:Label><br />

ID:

<asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'></asp:Label><br />

<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"

Text="New"></asp:LinkButton>

</ItemTemplate>

</asp:FormView>

&nbsp;</div>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:GD_CONN2 %>"

InsertCommand="INSERT INTO REUNIOES(DATA_P1, DATA_P2, [MIG/SIR], FRENTE, LIDER, ATIVIDADE, DATA_PLANEJADO, DATA_CONCLUSAO, DATA_REPLANEJAMENTO, STATUS, EQUIPE) VALUES&#13;&#10;( CONVERT (DATETIME, @.DATA_P1, 103),&#13;&#10;CONVERT (DATETIME, @.DATA_P2, 103),&#13;&#10;@.MIG, &#13;&#10;@.FRENTE, &#13;&#10;@.LIDER,&#13;&#10;@.ATIVIDADE,&#13;&#10; CONVERT (DATETIME, @.DATA_PLANEJADO, 103),&#13;&#10; CONVERT (DATETIME, @.DATACONCLUSAO, 103),&#13;&#10; CONVERT (DATETIME, @.DATA_REPLANEJAMENTO, 103),&#13;&#10;@.STATUS, &#13;&#10;@.EQUIPE)" SelectCommand="SELECT * FROM [REUNIOES] where id=@.id" CancelSelectOnNullParameter="False" EnableCaching="True">

<InsertParameters>

<asp:Parameter Name="DATA_P1" />

<asp:Parameter Name="DATA_P2" />

<asp:Parameter Name="MIG" />

<asp:Parameter Name="FRENTE" />

<asp:Parameter Name="LIDER" />

<asp:Parameter Name="ATIVIDADE" />

<asp:Parameter Name="DATA_PLANEJADO" />

<asp:Parameter Name="DATACONCLUSAO" />

<asp:Parameter Name="DATA_REPLANEJAMENTO" />

<asp:Parameter Name="STATUS" />

<asp:Parameter Name="EQUIPE" />

</InsertParameters>

<SelectParameters>

<asp:QueryStringParameter Name="id" QueryStringField="id" Type="Decimal" />

</SelectParameters>

</asp:SqlDataSource>

&nbsp;

</form>

</body>

</html>

Thanks, Jens.

|||Hi,

could that be that there is a trigger on the table you are inserting to which is cuasing the syntax error ? I can′t see any problems in the statement you posted here. Did you start the profiler to see which commands are executed against the database ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

I remember that was a function I′ve create to validate datetime. I′m analyzing ....

.

About the profiler. How can I start it? I never used it before.

|||Its located in the program folder (Start > Programs >...) the SQL Server profiler can trace exected statements as well as server events like exceptions etc. I will help you to investigate which command is finally executed against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The solution was create a new page with same configuration... :P

Thanks, Jens.