CODEWITHSUNDEEP
sqlsql-server-2008
raklos
raklos

Reputation: 28555

Stored procedure not updating field

I have a sproc like so, it basically appends a prefix to a field if it does not already exist:

ALTER PROCEDURE [dbo].[AgSp_UpdateAgTbl_Licensing ](@newPrefix nvarchar, @systemName nvarchar)

AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

update AgTbl_Licensing
set   UrlPrefixes =
           case
               when UrlPrefixes is null or UrlPrefixes = ''
               then @newPrefix
               else convert ( nvarchar( max ), UrlPrefixes) + ', '+@newPrefix
            end
where SystemName = @systemName and (UrlPrefixes not like ('%'+@newPrefix+'%') or UrlPrefixes is null)

END

I try and call this like so:

Execute AgSp_UpdateAgTbl_Licensing 'eb_', 'EB1';

but updates are not being made to the db - how come? i cant debug as i dont have sysadmin

Upvotes: 0

Views: 75

Answers (2)

Kaf
Kaf

Reputation: 33839

I think you should declare procedure parameters with length as;

@newPrefix nvarchar(50), @systemName nvarchar(50)

Otherwise it will take only FIRST character of your string and therefore your WHERE clause fails to find the records to be updated. And also your parameters are not what you expecting them to be.

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

(Ignoring that storing multiple values in a single column is almost always worse than properly storing them as rows in a table)

You've declare two parameters to your procedure, both of length 1. Try:

ALTER PROCEDURE [dbo].[AgTbl_Licensing](@newPrefix nvarchar(100), @systemName nvarchar(100))

From nchar and nvarchar:

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Upvotes: 1

Related Questions