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.
Author |
Topic |
BigMeat
Yak Posting Veteran
56 Posts |
Posted - 2006-01-08 : 19:09:49
|
HiI am creating some dynamic sql by passing variouse parametrs to my Stored Procedure. One of my Parameters is string has lots of values seperated by a comma to help build an 'IN' statement. SET @SQL = 'SELECT * FROM areas'SET @p1 = '10,20'If @p1 IS NOT NULLBEGINSET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'ENDThe above query runs perfecly well in Query Analyser, however when I put it into my ASP.NET application I get an error of "Error converting data type varchar to numeric."So I think I need to do some sort of casting or Converting but im not sure how to do it. Or do I need to use a INSTRING?I did manage to work out a method by using the follwoingSELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errorsSET @sql = @sql + ' WHERE PATINDEX(''%,'' + CAST(ArType AS VARCHAR) + '',%'','','' + @p1 + '','') > 0'IM strugging to understand all the ''' My TSQL is pretty basic, any help would be much appreciatedMany thanks in advance |
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-08 : 23:53:44
|
Hi,declare @sql as nvarchar use sp_executesql instead of execexec sp_executesql @sql ,N' @p1 varchar(1000)',@p1 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-01-09 : 00:32:16
|
>> "Error converting data type varchar to numeric."What is the data type for your column Areas ? From the error it looks like it is integer.If it is integer, you don't have to use replace(). SET @SQL = @SQL + ' WHERE Areas IN (' + @p1 + ')' -----------------'KH'Selamat Hari Raya Haji |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|