How to convert varchar values to int while passing as an in parameter?

meeram395

I have an sql query which gives the output as follows: OutputFileid ----------- 4 5 6 I want to get this output in horizontal fashion , like 4,5,6 so that I can pass to another query as an 'in' parameter. I was getting that, but the second query returned an error saying that "Conversion failed when converting the varchar value '4,5,6' to data type int". Following is the full stored procedure which i am using for getting the desired result.

ALTER procedure [dbo].[IsBCastRefExistsforProcess]
(@processId int)
as
begin
DECLARE @FILEIDs varchar(100)
select @FILEIDs= COALESCE(@FILEIDs+',' ,'')+ convert(varchar,outputfileid)+''from OutputFiles where ProcessId = @processid
PRINT @FILEIDs
if (LEN(@FILEIDs) > 0)
begin

select eg.EJVColumnId,eg.EJVColumnName
from EJVGedaColumns eg,
GedaColumnMapping gc
where eg.EJVColumnId = gc.EJVColumnId
and eg.EJVColumnName like '%BCAST_REF%'
and gc.OutputFileid in (@FILEIDs)
end
end

when I tried to execute exec IsBCastRefExistsforProcess 1234 it is giving the above error. Is there anyway which i can achieve this?

select @FILEIDs= COALESCE(@FILEIDs+',' ,'')+ convert(varchar,outputfileid)+''from OutputFiles where ProcessId = @processid

This gives me the values in horizontal fashion. I tried giving 'and gc.OutputFileid in (Convert(int,@FILEIDs)) - But end up with same error. Please help. Thanks

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

meeram395

I fixed it!!. I put the same in a dynamic sql and it got worked. Like this,

SET @SQL = 'select eg.EJVColumnId,eg.EJVColumnName,gc.OutputFileid from EJVGedaColumns eg,GedaColumnMapping gc
where eg.EJVColumnId = gc.EJVColumnId and eg.EJVColumnName like ''%BCAST_REF%''
and gc.OutputFileid in ('+@FILEIDs+')'

EXEC (@SQL)

Hope this will help somebody. Thanks

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

Lost User

As an alternative; you could create a function that returns[^] a table (with those ints). You could pass these even as parameters[^] :)

I are Troll :suss:

Hiren solanki

meeram395 wrote:

EXEC (@SQL)

other then EXEC you can also use sp_executesql procedure that will give you a way to input parameter with a strong datatype to avoid data mismatch conflict.

Regards, Hiren. Microsoft Dynamics CRM

My Recent Article: - Way to know which control have raised PostBack[^]

PIEBALDconsult

10!

Jorgen Andersson

This is one of the times that I'm sad that I work in Oracle. No such thing in Oracle as I know of.

"When did ignorance become a point of view" - Dilbert

Lost User

Thanks :-D

meeram395

Thanks very much Hiren, for the help. I will try that.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.

meeram395

Thank you very much Eddy. This is too good.

Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.