Problem in SQLQuery - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Problem in SQLQuery

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-04-13 : 02:23:48

I have created stored procedure like

--exec ABC '1,2'
CREATE PROCEDURE ABC
(

@STR VARCHAR(50)
)
AS
BEGIN

SET @STR = REPLACE(@STR,',',',''')
SET @STR = REPLACE(@STR,',',''',')
SET @STR = CHAR(39)+@STR+CHAR(39)
PRINT @STR

SELECT * FROM Table WHERE CONVERT(VARCHAR,column_name)
IN (@STR)

END

but problem is that table did not return any data even if data exists for that ID and in above stored procedure if I change query like below, then table returns the data

SELECT * FROM Table WHERE CONVERT(VARCHAR,column_name)
IN ('1','2')

Thanks in Advance

Kokkula
Starting Member

41 Posts

Posted - 2009-04-13 : 02:50:41

Hello,

Below is the sample code which will be solving your issue

Code:

CREATE TABLE [dbo].[Test]
(
[ID] INT,
[Name] NVARCHAR(50),
[Flag] INT
)

INSERT INTO [dbo].[Test] VALUES (1,'AAAA',1)
INSERT INTO [dbo].[Test] VALUES (2,'BBBB',2)

CREATE PROCEDURE [dbo].[Test]
(
@Active NVARCHAR(10)
)
AS
BEGIN
DECLARE @ActiveFlag NVARCHAR(50)

SET @ActiveFlag = ',' + CHAR(39) + REPLACE(@Active, ',',Char(39) + ',' + Char(39)) + Char(39) + ','
PRINT @ActiveFlag

SELECT ID
,Name
,Flag
FROM [dbo].[Test]
WHERE CAST(@ActiveFlag AS NVARCHAR) LIKE '%,'+ CHAR(39) + CAST(Flag AS NVARCHAR) + CHAR(39) + ',%'

END

EXEC [dbo].[Test] '1,2'

Hope helpful...

Thanks,
Pavan

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 07:18:51

try like this
declare @Table1 table(ID int, Name varchar(12), Amount decimal(18,2))
insert into @table1 select
1, 'AAA', 100 union all select
2, 'BBB', 200 union all select
3, 'CCC', 300 union all select
4, 'DDD', 400
declare @st varchar(32)
Set @st = '1,2,4'
select * from @table1 where '%,'+@st+',%' like '%,'+convert(varchar(32),id)+',%'

ruchijani
Starting Member

23 Posts

Posted - 2009-04-14 : 00:46:25

Hiii
This works fine
Thank u so much

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-14 : 07:59:23

quote:
Originally posted by ruchijani

Hiii
This works fine
Thank u so much

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources