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 2000 Forums
 Transact-SQL (2000)
 Concatenation Urgent Help Please

Author  Topic 

BigMeat
Yak Posting Veteran

56 Posts

Posted - 2006-01-08 : 19:09:49
Hi

I 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 NULL
BEGIN
SET @sSQL = @sSQL + ' WHERE (Areas IN (''' + Replace(@p1,',',''',''') + '''))'
END

The 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 follwoing

SELECT * FROM Areas WHERE PATINDEX('%,' + CAST(ArType AS VARCHAR) + ',%',',' + @p1 + ',') > 0

But I cant seem to convert the above line into coherent dynamic statement. My feeble attempt is below but I keep getting errors

SET @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 appreciated

Many 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 exec
exec sp_executesql @sql ,N' @p1 varchar(1000)',@p1
Go to Top of Page

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
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 02:39:59
or search for where in @MYCSV in this topic
http://sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -