Showing posts with label comma. Show all posts
Showing posts with label comma. Show all posts

Monday, March 26, 2012

How to Use Varchar with Int column

Hi,
I am writing a SP in MSSQL. One of the parameted is VARCHAR type
which have comma seperated INT vlaues. I want to use this varible in
WHERE clause against INT type coloum. how do I achive this.

eg. DECLARE @.CompanyTypeIDs VARCHAR(200)
SET @.CompanyTypeIDs = '1,3,2'

Currently I am using it as
SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)

CompanyTypeID is of INT type.
But I want to replace the consts with the Parameter passed as below

SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
(@.CompanyTypeIDs) - but this is not giving any result.

Please let me know the solution.

Many Thanks,
MaheshIf you want to continue along this path, then you need to construct
your entire SQL statement as a variable, and exec that, like the
following:

DECLARE @.CompanyTypeIDs VARCHAR(200)
SET @.CompanyTypeIDs = '1,3,2'

DECLARE @.SQL nvarchar (4000)
SET @.SQL = 'SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN ('
+ @.CompanyTypeIDs + ') '

PRINT @.SQL

EXEC(@.SQL)

However, this might not be the best solution to your problem, as I'm
sure someone else on this group will point out.

Stu|||Stu,
Thanks a lot for your promt response. Then what way do u suggest to
achive this. Actually I am getting @.CompanyTypeIDs as aparameter to
SP. So, please suggest accordingly.

My PS is quite big and above is one of the statement. I am suppose to
insert the data recieived from above statement into #temp TABLE.

Thanks,
Mahesh|||The method I suggested will work fine; all I meant to say was that
others might have a better suggestion than I. :)

Stu|||Thanks Stu,
yeah, currently I am using method suggested by you and is working great
for me.

Mahesh|||Curious to know if anyone have better way of achieving the above task.

Thanks,
Mahesh|||Mahesh (gaware@.gmail.com) writes:
> I am writing a SP in MSSQL. One of the parameted is VARCHAR type
> which have comma seperated INT vlaues. I want to use this varible in
> WHERE clause against INT type coloum. how do I achive this.
> eg. DECLARE @.CompanyTypeIDs VARCHAR(200)
> SET @.CompanyTypeIDs = '1,3,2'
> Currently I am using it as
> SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN (1,2,3)
> CompanyTypeID is of INT type.
> But I want to replace the consts with the Parameter passed as below
> SELECT CompanyTypeID FROM Company WHERE CompanyTypeID IN
> (@.CompanyTypeIDs) - but this is not giving any result.

Using dynamic SQL as suggested by Stu is a possible solution, but is
one of the poorest ones.

Look at http://www.sommarskog.se/arrays-in-...ist-of-integers
for a quick solution. If you want to explore other solutions, read the
entire article (which is quite long).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Stu (stuart.ainsworth@.gmail.com) writes:
> If you want to continue along this path, then you need to construct
> your entire SQL statement as a variable,

NO!!!!!

This is a possible solution, but there is no "need" to use it. It is
in fact one of the poorest solutions there is.

Please look at http://www.sommarskog.se/arrays-in-sql.html for a whole
range of alternatives, and why dynamic SQL is so bad.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, simmer down....

I figured there was a better way, and I also figured you or some of the
other posters would have a solution. Note that I prefaced my
suggestion with the tentative "if you want to continue down this
path...", implying that I understood that there were some weaknesses
with it.

Stu|||Yeah, Stu had mentioned it earlier about the performance of dynamic
SQL. Can anyone of you explain why Dynamic SQL is poor in performance?

Erland, actually my query consists for two such IN statements with AND
condition.

i.e WHERE StateTypeID in ('1,2,3,4....') AND UserTypeID IN
('2,3,4....')

StateTypeID and UserTypeID are of INT type. Could please let me know
the best way to perform the above query in SP.
Do I need to go for nested WHILE loop? what u feel is best way of doing
it

Thanks,
Mahesh|||Mahesh (gaware@.gmail.com) writes:
> Yeah, Stu had mentioned it earlier about the performance of dynamic
> SQL. Can anyone of you explain why Dynamic SQL is poor in performance?

When the list is long, it takes the optimizer very long time to build
the execution plan.

> Erland, actually my query consists for two such IN statements with AND
> condition.
> i.e WHERE StateTypeID in ('1,2,3,4....') AND UserTypeID IN
> ('2,3,4....')
>
> StateTypeID and UserTypeID are of INT type. Could please let me know
> the best way to perform the above query in SP.
> Do I need to go for nested WHILE loop? what u feel is best way of doing
> it

Did you look at the link I posted? That should take you a long way.
http://www.sommarskog.se/arrays-in-...st-of-integers-

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 7, 2012

How to use comma separated value list in the where clause?

How to use comma separated value list in the where clause?

I would like to do something like the following (Set voted = true for all rows in tblVoters where EmpID is in the comma separated value list).

update tbl_Voters

set voted = true

whereEmpID in @.empIdsCsv

Where, @.empIdsCsv = ’12,23,345,’ (IDs of the employees)

Since the above is not possible I have done the following dynamic query:

-- Convert the comma separated values to conditional statement like EmpID = {id} or EmpliD = {id}…

set @.empIdsCsv = 'EmpID=' + substring(@.empIdsCsv , 0, len(@.empIdsCsv ))-- Remove trailing comma

set @.empIdsCsv = replace(@.empIdsCsv , ',', ' or EmpID=')

declare @.markVoters varchar(8000)

set @.markVoters = '

update tbl_Voters

setvoted = true

where ’ + @.empIdsCsv

--Execute the dinamic query

exec (@.markVoters)

The above code generates the following dynamic query:

update tbl_Voters

setvoted = true

where

EmpID= 12 or EmpID=23 or EmpID=345

The obvious drawback here is the performance and the limitation of the dynamic query length (8000 chars).

Can someone suggest a better solution with the ability to use comma seperated values in the where clause?

there is the IN clause however I somehow get the feeling that will not help performance that much. The syntax is
select columns from tablename where EMplID IN (12, 23, 356, ....)

Hope this helps
|||

Please take a look at the link below that describes various techniques on how to pass a list of values from client to SQL Server.

http://www.sommarskog.se/arrays-in-sql.html

How to use comma separated lists with parameters in sqldatasource?

I'm new (very new) to asp.net and now at 3am after maybe 12 straight hours of trying to go through examples and understand the syntax, I have a somewhat working program... basically a query parameters screen that upon a click generates a report. The query parameter screen basically is a HTML form with a procedure that puts the field values into session variables. The report uses <asp:sessionparameters> in the sqldatasource to apply aforementioned session variables to filter the SELECT statement's results. They are then displayed using the gridview control. It actually works!

Now I'm trying to figure out: if I need/want my users to be able to enter comma-separated list of values into the HTML form field and somehow get these into the SELECT statement's WHERE clause, is there a way to do this? I'm not very experienced with asp nor sql server and have been struggling with this new challenge. I really could use pointers as to whether there's an easy way to handle this. In other languages in the past I would have had to parse the comma-separated list and use those to construct my SELECT statement programmatically. I get the impression that with all this ASP.NET fanciness, there has to be a better way?

If it matters, I'm using ASP.NET 2.0 with SQL Server 2005 Express and Visual Studio Web 2005 Express.

Thanks in advance for any replies, they would be GREATLY appreciated :)

Hi,

you either generate the SQL query programmatically based on the CSV, e.g split the CSV string with the delimiter, loop the array returned by String.Split and then buold the SQl string dynamically. Other way is to create a stored procedure to the Db and use functions to split CSVs for example:http://www.sqlteam.com/item.asp?ItemID=11499