Dynamic Sql Stored Proc Truncating Values

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 2000 Forums

SQL Server Development (2000)

Dynamic Sql Stored Proc Truncating Values

Author

Topic

jspurlin
Starting Member

5 Posts

Posted - 2004-09-14 : 11:18:26

This stored procedure is truncating any string value to 30 characters. Here are the values I am entering followed by the procedure itself:

//TableName = 'ModuleSettings'
//ModuleId = '9'
//Setting names = 'xmlsrc', 'xslsrc', 'css'
//Setting values = '~/XmlData/NewsLinks.xml'
// '~/Transforms/NewsLinksUtility.xslt'
// '~/Styles/NewsLinksUtility.css'

//-> '~/Transforms/NewsLinksUtility.xslt' is stored as
// '~/Transforms/NewsLinksUtility.' every time

In code the correct Parameter value types are provided:
SqlDbType.Int, 4
SqlDbType.NVarChar, 50
SqlDbType.NVarChar, 256 (which is where the truncated statement happens to appear).

And these parameters are matched by the design of the table.

This is my first forray into Dynamic SQL and the may be some rule I am missing with regards to the special characters in the strings I am sending.

Here is the stored procedure with which this forum has so kindly helped me:

CREATE PROCEDURE UpdateModuleSettings
(
@TableName nvarchar(128),
@ModuleId int,
@SettingName nvarchar(50),
@SettingValue nvarchar(256)
)
AS

Declare @SQL nvarchar(2250)

SELECT @SQL = 'IF NOT EXISTS '
SELECT @SQL = @SQL + '(SELECT * FROM '
SELECT @SQL = @SQL + @TableName
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''')'

SELECT @SQL = @SQL + 'INSERT INTO ' + @TableName + ' (ModuleId, SettingName, SettingValue) VALUES '
SELECT @SQL = @SQL + '(' + CONVERT(NVARCHAR,@ModuleId) + ',''' +
CONVERT(NVARCHAR,@SettingName) + ''', ''' + CONVERT(NVARCHAR,@SettingValue)
+ ''') '

SELECT @SQL = @SQL + 'ELSE UPDATE ' + @TableName
SELECT @SQL = @SQL + ' SET SettingValue = ''' + CONVERT(NVARCHAR,@SettingValue) + ''''
SELECT @SQL = @SQL + ' WHERE ModuleId = ' + CONVERT(NVARCHAR,@ModuleId) + ' AND SettingName = ''' + CONVERT(NVARCHAR, @SettingName) + ''''

Exec ( @SQL)
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 11:35:28

try changing
CONVERT(NVARCHAR,@SettingValue)
to
CONVERT(NVARCHAR(256),@SettingValue)

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-14 : 13:05:48

Why do you have to CONVERT varchar to varchar?

Brett

8-)

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-15 : 04:26:45

quote:
partial sample code...SELECT @SQL = @SQL + N' SET SettingValue = ''' + ~~CONVERT(NVARCHAR,~~@SettingValue) + N''''

note:
as spirit said, convert(nvarchar(n),@variable) without specifying n defaults to nvarchar(30) !
as Brett said, why convert in the first place ?
N'sometext' - the N specifies that 'sometext' is a unicode string.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources