how to remove single quotes on passing value?,

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

Transact-SQL (2005)

how to remove single quotes on passing value?,

Author

Topic

Mageshkumar
Starting Member

29 Posts

Posted - 2008-09-02 : 07:56:26

Hi i deaclared one variable as nvarchar(max)

the values coming like this

566,577,588

so i declared nvarchar(max)

now i am passing this value to my query:this is my query

UPDATE [dbo].[TBL_EDITVOLUMEDATA]
SET [EditFlag] = 'Y',
[Remarks]=@iRemarks
,[MdfdDT] = GetDate()
,[MdfdBY] = @iCrtnBy
WHERE [UploadID] in (@iUploadID )

Its nvarchar so automatically query will come like this:

UPDATE [dbo].[TBL_EDITVOLUMEDATA]
SET [EditFlag] = 'Y',
[Remarks]=@iRemarks
,[MdfdDT] = GetDate()
,[MdfdBY] = @iCrtnBy
WHERE [UploadID] in ('566,577,588')
But i have to pass like this in that place:

UPDATE [dbo].[TBL_EDITVOLUMEDATA]
SET [EditFlag] = 'Y',
[Remarks]=@iRemarks
,[MdfdDT] = GetDate()
,[MdfdBY] = @iCrtnBy
WHERE [UploadID] in (566,577,588)

It mean have to remove that single quotes on that value place

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 08:07:15

See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 08:09:46

Or maybe this simple?

UPDATE	tbl_EditVolumeData
SET	EditFlag = 'Y',
	Remarks = @Remarks
WHERE	',' + @UpLoadID + ',' LIKE '%,' + UploadID + ',%'

E 12°55'05.63"
N 56°04'39.26"

Mageshkumar
Starting Member

29 Posts

Posted - 2008-09-02 : 08:12:16

No really i didnt get plz help me...,

Instead of giving this '568,555'

i want to pass like this 568,555

quote:
Originally posted by Peso

See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.63"
N 56°04'39.26"

Mageshkumar
Starting Member

29 Posts

Posted - 2008-09-02 : 08:21:43

Hi peso thanks for ur reply...,

Actually this uploadid is numeric one...,

But from my interface to my stored procedure i am passing as nvarchar(max),

If i exe the stored proc i am getting error as "Error converting datatype varchar to numeric"

tell me to solve this...,

This is my stored procedure:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SP_SALES_EDITFLAG]
(
@iUploadID nvarchar(max),
@iRemarks varchar(max),
@iCrtnBY int,
@oError int output ,
@oErrorMsg Varchar(MAX) output
)

AS
BEGIN
SET NOCOUNT ON;
declare @test nvarchar(max)
UPDATE [dbo].[TBL_EDITVOLUMEDATA]
SET [EditFlag] = 'Y',
[Remarks]=@iRemarks
,[MdfdDT] = GetDate()
,[MdfdBY] = @iCrtnBy
WHERE ',' + @iUpLoadID + ',' LIKE '%,' + UploadID + ',%'

INSERT INTO [dbo].[TBL_SALEVOLUMEDATALOG]
SELECT * FROM [dbo].[TBL_SALEVOLUMEDATA]
WHERE ',' + @iUpLoadID + ',' LIKE '%,' + UploadID + ',%'

DELETE FROM [dbo].[TBL_SALEVOLUMEDATA]
WHERE ',' + @iUpLoadID + ',' LIKE '%,' + UploadID + ',%'

IF @@ERROR<>0
BEGIN
SET @oErrorMsg = 'Error in setting the editable flag to True'
SET @oError = -1
GOTO ERROR_EXIT
END
ELSE
BEGIN
SET @oErrorMsg = 'Sales Data Updated Successfully'
SET @oError = 0
END
END;
NORMAL_EXIT:
RETURN
ERROR_EXIT:
RETURN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 08:34:26

use
WHERE ',' + @iUpLoadID + ',' LIKE '%,' + cast(UploadID as varchar(10))+ ',%'

wherever applicable

Madhivanan

Failing to plan is Planning to fail

Mageshkumar
Starting Member

29 Posts

Posted - 2008-09-02 : 08:52:30

Thank u Guys its working fine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-02 : 09:02:27

If you have spaces in the supplied string, use REPLACE

WHERE ',' + REPLACE(@iUpLoadID, ' ', '') + ',' LIKE '%,' + CAST(UploadID AS VARCHAR(20)) + ',%'

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-09-02 : 09:05:31

quote:
Originally posted by Mageshkumar

Thank u Guys its working fine

What about other threads that you didnt follow?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources