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