StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Just to expand on what @PaulDraper and OzrenTkalcec have said, <a href="http://technet.microsoft.com/en-us/library/ms188655.aspx#sectionToggle1" rel="nofollow noreferrer">Sql PROCs may only return <code>INT</code></a></p> <p>So when you return an NVARCHAR SQL is trying to convert it to INT, which is overflowing (It is possible that this procedure has worked correctly previously if the return value has been small enough to fit into an <code>INT</code>, e.g. with a small value for <code>@PartNumber</code>)</p> <p>You have several options:</p> <p><strong>Bind an Output variable</strong></p> <pre><code> ALTER PROCEDURE [dbo].[_MG_NextInventoryPackNumberForPartNumber] @sPartNumber NVARCHAR(254), @sNewInventoryPackNum NVARCHAR(254) OUTPUT AS ... </code></pre> <p>and then <code>SET @sNewInventoryPackNum = @sPartNumber + @StringNum + '10'</code> will be the last line of the PROC. The caller will then need to bind to the Output parameter (e.g. <code>ParameterDirection.Output</code> from ADO)</p> <p><strong>Select the <code>@sNewInventoryPackNum</code> as a result</strong>, instead of returning it. This will then be a scalar resultset available to the caller (e.g. retrieve in ADO as a Scalar <a href="http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx" rel="nofollow noreferrer"><code>.ExecuteScalar</code></a>)</p> <p><strong>Use a <a href="http://technet.microsoft.com/en-us/library/ms186755.aspx" rel="nofollow noreferrer">UDF</a></strong> with a <code>NVARCHAR</code> return value</p> <p>As an aside, it appears that a major part of your <code>PROC</code> is concerned with padding with leading zeroes to keep a fixed string width. There are several alternatives, such as using <a href="https://stackoverflow.com/questions/9520661/formatting-numbers-by-padding-with-leading-zeros-in-sql-server"><code>STUFF</code></a>, which offer more elegant ways of achieving this IMO.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload