With SQL Server 2008, I'd like to add a variable (@wfID
) into a text, like below:
DECLARE @wfID uniqueidentifier
SET @wfID = NEWID()
'<META http-equiv="Content-Type" content="text/html; " /> <br><input type="button"
value="" onclick="window.open("http://localhost/TestWeb2/Test_Look.aspx?Test_ID='
+ convert(nvarchar, @wfID)
+ '");" /></br>',
So, I'd like to add the @wfID
to the text but it always says
The data types nvarchar and text are incompatible in the add operator.
I tried converting everything into nvarchar, but then I got this:
Arithmetic overflow error converting expression to data type nvarchar.
Any suggestions?
The key difference between varchar and nvarchar is the way they are stored, varchar is stored as regular 8-bit data(1 byte per character) and nvarchar stores data at 2 bytes per character. Due to this reason, nvarchar can hold upto 4000 characters and it takes double the space as SQL varchar.
The NVARCHAR data type stores strings of varying lengths. The string can include digits, symbols, and both single-byte and (in some locales) multibyte characters. The main difference between VARCHAR and NVARCHAR data types is the collation order.
nvarchar [ ( n | max ) ] max indicates that the maximum storage size is 2^30-1 characters (2 GB). The storage size is two times n bytes + 2 bytes. For UCS-2 encoding, the storage size is two times n bytes + 2 bytes and the number of characters that can be stored is also n.
The syntax for declaring the nvarchar variable is nvarchar(n), where n defines the string size in byte-pairs. The value of n must be from 1 through 4000. For Example, when we store a string of length 10, The string will occupy (10*2) + 2 (=22 bytes) bytes of storage.
In your call to convert you try to convert to just nvarchar which implicitly means nvarchar(1), i.e. there is not room for the whole guid to be converted.
Change that to
convert(nvarchar(36), @wfID)
and it will work.
For some strange reason MSSQL gives an arithmetic overflow if doing SELECT CONVERT(nvarchar,NEWID())
but gives a proper 'Insufficient result space' if doing SELECT CONVERT(varchar,NEWID())
.
This is working fine'
--EXECUTE usp_TEST
CREATE PROCEDURE [dbo].[usp_TEST](
@UserID nvarchar(max) ='82F1A3A6-4DC6-481F-9046-856270E66468'
)
AS
BEGIN
declare @sqlwhere1 nvarchar(max)=''
SELECT @sqlwhere1= 'SELECT *
FROM [yourtablename] WITH(NOLOCK) WHERE '
IF(@UserID <> '')
BEGIN
SELECT @sqlwhere1=@sqlwhere1 +' convert(nvarchar(max),UserId)= '''+convert(nvarchar(max),@UserID)+''''
END
EXECUTE (@sqlwhere1)
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With