July 24, 2009 at 8:51 am
Hello, I am getting an 102 error message, when i execute the user defined procedure :
====
EXEC USP_UpdateAllotUser '5', 'hello', '10000'
====
The procedure should update the 'Acc5' column with new content 'hello'.
The procedure code as well as the table is given below:
=================================
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- --------------------------------------------------------------------------------------------------
-- Procedure : USP_UpdateAllotUser
-- Parameters : @accno, @acc, @userid
-- Description : Procedure to Update AllotUser table
-- Execute : EXEC USP_UpdateAllotUser @accno='5', @acc='ABCD', @userid='10000'
-- Author : Bhavesh Patel
-- Create date : 22-07-2009
-- Edit History :
-- Copyright :
-- --------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[USP_UpdateAllotUser]
(
@accno NVARCHAR(5),
@acc NVARCHAR(50),
@userid NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
IF (@accno <> '' OR @acc <> '' OR @userid <> '')
BEGIN
DECLARE @sqlquery VARCHAR(1000)
SET @accno = 'Acc'+@accno
SET @sqlquery = 'UPDATE Allotuser SET ('+QUOTENAME(@accno)+')='+@acc+' where userid = '+@userid
EXEC(@sqlquery)
END
END TRY
BEGIN CATCH
PRINT @@ERROR
END CATCH
END
=============
Table to be updated is as follows:
srno|username|userid|password|Acc1|Acc2|Acc3|Acc4|Acc5|Acc6
-------------------------------------------------------------------------
200025|10000|10000|bhavesh|a100|a104|a107|a301|ABCD|NULL
Please help!
July 24, 2009 at 9:13 am
oops... researching now...what was the erro message specifically...error numbers not much help.
Lowell
July 24, 2009 at 9:20 am
ahh now i see; it's kind of obvious.
best practice is to always print your sql before you execute it:
this is the SQl statement your code creates:
UPDATE Allotuser SET ([Acc5])=hello where userid = 10000
can you see what is wrong with it?
Lowell
July 24, 2009 at 9:26 am
a rewrite of your proc to have better error messaging:
ALTER PROCEDURE [dbo].[USP_UpdateAllotUser]
(
@accno NVARCHAR(5),
@acc NVARCHAR(50),
@userid NVARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
IF (@accno '' OR @acc '' OR @userid '')
BEGIN
DECLARE @sqlquery VARCHAR(1000)
SET @accno = 'Acc' + @accno
SET @sqlquery = 'UPDATE Allotuser SET ('+QUOTENAME(@accno)+')='+@acc+' where userid = ' + @userid
PRINT(@sqlquery)
EXEC(@sqlquery)
END
END TRY
BEGIN CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage NVARCHAR(4000),
@ErrorState INT
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
IF @ErrorState = 0
SET @ErrorState = 1
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)
END CATCH
END
GO
EXEC USP_UpdateAllotUser '5', 'hello', '10000'
Lowell
July 24, 2009 at 9:40 am
hi Lowell
When i print the @sqlquery it is -
UPDATE Allotuser SET ([Acc5])=hello where userid = 10000
where as correct query should be --
UPDATE Allotuser SET Acc5='hello' where userid='10000'
How should i get the above query in query string????
Bhavesh
July 24, 2009 at 9:55 am
two single quotes in a row are the "escape" sequence to insert into a quote delimited string:
SET @sqlquery = 'UPDATE Allotuser SET ' + QUOTENAME(@accno) + '=''' + @acc + ''' where userid = ''' + @userid + ''' '
Lowell
July 24, 2009 at 9:59 am
Many thanks Lowell. It works perfect now
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy