SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Concatenation Urgent Help Please		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

BigMeat
Yak Posting Veteran

56 Posts
Posted - 01/08/2006 :  19:09:49  Show Profile  Reply with Quote
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

India
394 Posts
Posted - 01/08/2006 :  23:53:44  Show Profile  Reply with Quote
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)

Singapore
16746 Posts
Posted - 01/09/2006 :  00:32:16  Show Profile  Reply with Quote
>> "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

India
22460 Posts
Posted - 01/09/2006 :  02:39:59  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000