Using Multiple Values In IN CLAUSE

Jul 26, 2006

how can i use multiple values in IN CLAUSE in a SQL query, that too when the number of values are changing at runtime.

complete SQL code required...............

I have following picture in mind but the values(in IN clause) are changing at Runtime

DECLARE @groups TABLE (group_id int)

SELECT * FROM abc WHERE abc_id IN (SELECT group_id FROM @groups)

View 4 Replies


Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?


SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')

if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:

CREATE PROC sp_test @in_company_code nvarchar(1024)

select company_code, name, description
from member_company
where company_code in (@in_company_code)

However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?


View 2 Replies View Related

Reporting Services :: Selecting Multiple Parameters Values For Comma Separated Values In SSRS?

Jun 17, 2012

I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.

View 5 Replies View Related

Sum Values With A Where Clause?

Aug 3, 2007

First of all Im a VB beginner, real beginner, like today is my first time hahaha
My problem is that I dont know how to put my question into VB code. Here goes.
Im working in Visual Studio and Reporting Services with a chart. I have a chart that shows the amount of trips an opportunity has in my CRM system. The opportunity goes from Open to Won or lost but I dont want to see those.

What I want to do in this chart is to separate all the Open Opportunities from the Won ones. I want to use a Stacked Column chart so Open Opportunities is on the upper part and Won is on the lower part of the column of course.

I can get the trips data from the CRM database, but I dont know how to put it in VB code Sum the trips where Opportunity=Won? for one series and for the other Sum the trips where Opportunity=Open?. In the database I have fields like statecodename='Won'. So far I have in the Data Values, were I write my expression:
Opportunity Open Trips =Sum(Fields!trips.Value)
And the same for Opportunities Won Trips but that isnt right of course.

So how do I say =Sum(Fields!trips.Value) Where statecodename='Won'??

View 6 Replies View Related

Retrieving Multiple Values From One Field In SQL Server For Use In Multiple Columsn In Reports

Mar 30, 2007

I am trying to create a report using Reporting Services.

My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.

I can currently get one value but how to get the information I need to be able to use in my reports.

So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.

Any help would be appreciated.


I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.

What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report

As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.

As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem

Thanks for the suggestions that were made, I apoligize for not making the problem clearer.

Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.

--Pulls the Service Opportunity

SELECT cs.value AS "Service Opportunity"

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE = 'Service Opportunity'

--Pulls the Number of Hours

SELECT cs.value AS 'Number of Hours'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ='Num of Hours'

--Pulls the Person Grade Level

SELECT cs.value AS 'Grade'

FROM Cstudent cs

INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid

WHERE ='Grade'

--Pulls the Person Number, First and Last Name and Grade Level

SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"

FROM student s

INNER JOIN cperson cs ON cs.personid = s.personid

INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid

WHERE cs.value =(SELECT cs.value AS 'Grade'

WHERE ca.attributeid = cs.attributeid AND'Grade')

View 11 Replies View Related

How To Use Multle Values For Where Clause

Oct 14, 2007

Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.
 I have the following tables
tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing
 bid_id      Uci_id
123           1000
123           2000
123           1050
tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.
Uci_ID       Sub_ID
1000          456
1000          2345
2000          456
1050          2345
2000          2345
This is the statement I am using to return the Uci's from the Bid table with bid_id of 123.  For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.
SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.
Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.

View 4 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007


I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as








Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use




Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA






Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

Using A UDF To Return Values For A Dynamic WHERE IN () Clause

Mar 2, 2006


I've search around quite extensively on the net and found a few examples that touch on this subject, but the only definitive one that seemed to solve this problem used a temp table in the UDF, which, to my knowledge, is impossible...

The problem is thus:
I want to create either a stored procedure or a user defined function to return a list of values I can intersperse to use in a WHERE AccountID IN (<values>). This way, if someone were to create a new stored procedure and they wanted to either only select accounts with those IDs or perform a NOT IN and use it to filter.

The Solution I'm attempting:
My idea is best represented in psuedo-code:
- Create a Function that stores all account Ids we relate to a particular account type, in this case, let's say accountsids "100, 101, 102, 407" are all accounts we want to consider "cash".
- The function would look something like:



DECLARE TABLE @t1 (account INT)

Then I could call this function by doing something such as:

FROM Accounts
WHERE AccountId IN (dbo.CashAccountIds())

I would presumably do this for other collections of accounts as well, so that I would end up with say 5 functions I could call to filter various types of accounts.

Not too certain if I am approaching this the correct way or not, I've been receiving a myriad of errors trying different methods. If I use the function above it tells me "Must declare @t1", so I modified it so @t1 is declared in the RETURNS statement, and the syntax checks then work, but when I attempt to save the function it tells me "Cannot perform alter on fn_cashaccountids because it is an incompatible object type"

(The code I use to generate this error is:
CREATE FUNCTION fn_cashaccountids ()




Hopefully I've provided enough but not too much info to sift through, it seems to me this would be something encountered a bit before.

Any help is very much appreciated.

- Jeff

View 3 Replies View Related

Four Different Values In The 'where' Clause (was SQL Query Question)

Sep 28, 2006

Hello everyone. This is my first post here, so be gentle =)

I need to construct a query, that would return roughly 6000 rows of data.

There are some conditions, or joins, that I can't figure out. Maybe you could help me?

This is the first one.

Invoice.ID-DocParty.DocumentID -> DocParty.OtherID-Party.ID -> Party.IDNumber

This can be achieved with inner join, no problem. Pretty simple.

However, there's a catch =)

DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?

There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.

In addition, there is another type of condition that needs to be fullfilled.

Invoice.Type1Account-Account.ID -> Account.Number
Invoice.Type2Account-Account.ID -> Account.Number

Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?

Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du

View 13 Replies View Related

Adding Values To A Parameter That Can Take Multiple Values

Jun 6, 2007

If I have a Select statement like this in my C# code:
Select * From foods Where foodgroup In (@foodgroup)
And I want @foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?
I tried
meat, dairy, fruit
'meat', 'dairy', 'fruit'
but neither worked. Is this possible?

View 2 Replies View Related

'Insert Into' For Multiple Values Given A Table Into Which The Values Need To Go

Sep 1, 2007

Please be easy on me...I haven't touched SQL for a year. Why given;

Code Snippet
USE [Patients]
/****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/
CREATE TABLE [dbo].[Patients](
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,

do I get

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ','.
for the following;

Code Snippet
(ID, FirstName,LastName,DOB) VALUES
( '1234-12', 'Joe','Smith','3/1/1960'),
( '5432-30','Bob','Jones','3/1/1960');

Thank you,

View 3 Replies View Related

SqlDataReader - Pulling Multiple Values Into Multiple Variables

Mar 29, 2007

Hello all,I'm trying to request a number of URLS (one for each user) from my database, then place each of these results into a separate string variables. I believed that SqlDataReader could do this for me, but I am unsure of how to accomplish this, or if I am walking down the wrong road. The current code is below (the section in question is in bold), please ignore the fact that I'm using MySQL as the commands work in the same way. public partial class main : System.Web.UI.Page{    String UserName;    String userId;    String HiveConnectionString;    String Current_Location;    ArrayList Location;    public String Location1;    public String Location2;    public String Location3;    //Int32 x = 0;    private void Page_Load(object sender, EventArgs e)    {        if (User.Identity.IsAuthenticated)        {            UserName = Membership.GetUser().ToString();            userId = Membership.GetUser().ProviderUserKey.ToString();            HiveConnectionString = "Database=hive;Data Source=localhost;User Id=hive_admin;Password=West7647";            using (MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(HiveConnectionString))            {                // Map Updates                 MySql.Data.MySqlClient.MySqlCommand Locationcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT Location FROM tracker WHERE Location = IsOnline = '1'");                Locationcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Locationcmd.Connection = conn;                conn.Open();                MySql.Data.MySqlClient.MySqlDataReader LocationReader = Locationcmd.ExecuteReader();                 while (LocationReader.Read())                {                        Location1 = LocationReader.GetString(0);                        //Location2 = LocationReader.GetString(1); // This does not work..                }                                LocationReader.Close();                conn.Close();                // IP Display                MySql.Data.MySqlClient.MySqlCommand Checkcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "SELECT UserName FROM tracker WHERE PKID = ?PKID");                Checkcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                Checkcmd.Connection = conn;                conn.Open();                object UserExists = Checkcmd.ExecuteScalar();                conn.Close();                if(UserExists == null)                {                    MySql.Data.MySqlClient.MySqlCommand Insertcmd = new MySql.Data.MySqlClient.MySqlCommand(                        "INSERT INTO tracker (PKID, UserName, IpAddress, IsOnline) VALUES (?PKID, ?Username, ?IpAddress, 1)");                                        Insertcmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Insertcmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Insertcmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Insertcmd.Connection = conn;                    conn.Open();                    Insertcmd.ExecuteNonQuery();                    conn.Close();                }                else                {                    MySql.Data.MySqlClient.MySqlCommand Updatecmd = new MySql.Data.MySqlClient.MySqlCommand(                        "UPDATE tracker SET IpAddress = ?IpAddress, IsOnline = '1' WHERE UserName = ?Username AND PKID = ?PKID");                    Updatecmd.Parameters.Add("?IpAddress", MySql.Data.MySqlClient.MySqlDbType.VarChar, 15).Value = Request.UserHostAddress;                    Updatecmd.Parameters.Add("?Username", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = UserName;                    Updatecmd.Parameters.Add("?PKID", MySql.Data.MySqlClient.MySqlDbType.VarChar, 255).Value = userId;                    Updatecmd.Connection = conn;                    conn.Open();                    Updatecmd.ExecuteNonQuery();                    conn.Close();                }            }        }    } Can anyone advise me on what I should be doing (even if its just a "you should be using this command) if this is not correct? In fact any pointers would be nice !Thanks everyone! 

View 1 Replies View Related

Multiple Where Clause?

Jun 11, 2007

where exigo_data_sync.orderdetail.itemcode in (B1001, B1001B, B1007, B1007B, B1008, B1008B, B1000, B1000B, B1006, B1006B, B1009, B1009B)

I keep getting a ADO error stating invalid column names...these are not column names they are the data that i want to use in the where clause. What am I doing wrong?

View 2 Replies View Related

How To Pass Values For The In Clause To The Stored Procedure?

Apr 7, 2008

hi friends,i need to select some of the employees from the EmpMaster using in clause. I tried to pass a string with the comma delemeters. it didn't produce all the records except the first in that string.shall i try with string functions in TSQL or any other options? Thanks and Regads,Senthilselvan.D 

View 4 Replies View Related

Sql WHERE Clause With Multiple Parameters

Jun 28, 2006

hello. I have a database that a client developed that I need to pull data from. it consists of articles that fall into a range of 3 main categories. each article will have up to 7 different subcategories they fall into. I need to be able to sort by main category as well as by subcategory. But when I create the SQL query it gets really messy. I tried using WHERE  Cat1= comm OR leg OR and so on, but there are seven categories so this gets very cumbersome and doesn't quite work. Is there a way to create an array or a subquery for this? I am a total newbie, so any help is much appreciated!

View 2 Replies View Related

Multiple Join Clause

Feb 12, 2004

I have a table "Users" like this:


I need to query the users getting the company's and group's names, but I only know how to join one table. Example:

Select UserId, GroupId, Groups.Name, CompanyId, Companies.Name
From Users JOIN Groups ON Users.GroupId = Groups.Id

Hon can I add the companies table in the Join ?


View 2 Replies View Related

Multiple Criteria In WHERE Clause (was T-SQL)

Oct 5, 2005

How can you handle multiple criteria query in T-SQL ? i wrote selection query and in my where clause i have about 7 different criteria and for some reason when i run the query i do not get any error but i do not get any data return.

So is there any other way to handle multiple criteria in T-SQL ?

View 12 Replies View Related

Where Clause With Multiple Cases

Nov 28, 2012

I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.

A parameter in my stored procedure "@intUserType" may contain values 0-3

If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records

The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.

This is what I am working with:

SELECT * FROM tblEmployees
WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))

Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.

View 4 Replies View Related

WHERE Clause With Multiple ANDs

Apr 2, 2015

I have a table (tblAttributes) that looks like this:


I need to return all the distinct IDs where the combination of AttrID and AttrVal matches multiple criteria for that specific ID.

I have the following:

FROM tblAttributes AS a
WHERE a.AttrID = 90 AND a.AttrVal = 7) AS x
FROM tblAttributes AS a
WHERE a.AttrID = 91 AND a.AttrVal = 8) AS y
ON x.ID = y.ID)

View 5 Replies View Related

Multiple Where Clause On One Report

Aug 14, 2007

Hello People, Please help. I have a basic report with a parameter in the 'Where" clause called (@Stat) from the statement below:
" WHERE contractinfo.termdate >= GETDATE()
AND provider.status= 'Active' AND provider.credentialstatus = (@Stat)"

This variable has one of two values: 'A' or 'B' that the user selects, how do I set it up so that if user selects say 'A' then the Where clause would go to one set of constraints ie
"WHERE contract.description NOT LIKE 'NON%' "

But if the user selects 'B' then the Where clause would go to a different set of constraint ie
"WHERE contract.description LIKE 'NON%' "

View 4 Replies View Related

Showing Null Values From ON Inner Join Sproc CLAUSE

Jul 19, 2006

Hi all
My query has some inner joins to some tables. And problem is when any ON clause get null as value, the correspondent record is not displayed.
SELECTTableA.A, TableB.AFROM TableAINNER JOIN TableB ON TableA.A = TableB.A
What I did try:
(but It generates redundant values from TableB)
I need to show all values even that value from Tablea is null
Thank a lot for any help

View 1 Replies View Related

Select Statement Using Multi-list Box Values For WHERE IN SQL Clause

Jan 11, 2007

I have a gridview that is based on the selection(s) in a listbox.  The gridview renders fine if I only select one value from the listbox.  I recive this error though when I select more that one value from the listbox:
Syntax error converting the nvarchar value '4,1' to a column of data type int.  If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly.
<asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName">
<asp:Parameter Name="ListSelection" DefaultValue="1"/>
The selListID column is type integer in the database.
I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result:
Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
Dim Item As ListItem
For Each Item In ListBox1.Items
If Item.Selected Then
If Label1.Text <> "" Then
Label1.Text = Label1.Text + Item.Value + ","
Label1.Text = Item.Value + ","
End If
End If
Label1.Text = Label1.Text.TrimEnd(",")
SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID)
End Sub
What am I doing wrong here?  Thanks!

View 4 Replies View Related

Use TextBox Values In WHERE Clause To Perform Date Search?

Mar 17, 2008

 I want to have a FromDateTextBox and a ToDateTextBox where the user can enter in dates (most likely in mm/dd/yy format, although intelligently handing other formats might be a plus).  Then I want to use these dates as the basis for a WHERE clause like:<some sql...> WHERE start_date BETWEEN  'FromDateTextBox.Text' AND 'ToDateTextBox.Text' (Note this WHERE clause will be used as the basis for an SqlDataSource FilterExpression).  1. I believe the date strings need to be in the format 'yyyy-mm-dd' to search SQL server is this correct?2. What's a decent way to convert the strings from the textboxes to the required format?3. How can I avoid an SQL injection attack? 

View 4 Replies View Related

SQL7: Order Of Values In IN Clause Affects Results

Dec 14, 1999

Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)

returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)

The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)

Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...

View 2 Replies View Related

Transact SQL :: Using A Variable Containing Comma Separated Values In IN Clause?

Jun 12, 2015

I am try to use a variable containing comma separated values in the IN clause.

View 10 Replies View Related

IN Clause With 32000 Values Shuts Down SQL Server Service

Sep 21, 2007


SQL Server 2000 Enterprise, 2 clustered nodes on Windows Server 2003

A web application ran a query based on the selection by the end user-> dynamically generated query with IN clause including + 32000 values

-> SQL Server Services shut down

Found this:

Stack overflow occurs when you run a query that contains a large number of arguments inside an IN or a NOT IN clause in SQL Server

Is there a way to secure SQL Server from queries like that?
Many thanks!


View 3 Replies View Related

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Best Practice: Use Values In WHERE Clause Or Create And Join Temp Table ?

Dec 28, 2006

I am using a SQL back end to dynamically populate an report/page.
As the data I'm interrogating is created from a tree control, I'm having to use a recursive function to retrieve the data into a series of ID values. This all happens at the moment in a DataTable manipulated with c# code. So my ID values end up in this datatable.
 My problem is that I am then performing a crosstab query in SQL Server 2000 and these ID are required as part of that query.
 Should I create a temp table and join this into the query or should i feed in a series of ID values into a where clause?
 Any help gratefully appreciated.

View 2 Replies View Related

ORDER BY Clause With Multiple Tables

Aug 25, 2005

Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM   Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId                       LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden

View 6 Replies View Related

Like Clause - Preventing Multiple Cases

Sep 9, 2013

I have a like clause like this:


I want to know if it is possible just have one like clause from 1-9:

CAT1, CAT3, ...., CAT9

View 3 Replies View Related

Stuck With WHERE Clause With Multiple Elements

Jul 20, 2005

HiI'm a bit stuck with a SELECT query. This is a simplified version ofwhat I need. I've had a look in a few books and online but I'mdefinitely missing something. I'm trying to avoid looping and cursors.I'll be running this in a stored procedure on SQL 7.I have a separate query which returns a series of numbers, A, say 101103 107 109 113.I have a table (tableB) with a field myFieldB where I have anotherseries of numbers, B. I want return each row in tableB wherei - ALL values in A existii- ANY values in A existFor ii, I can use WHERE myFieldB IN AHow about for i?Is there a good guide on the web or a book on WHERE clauses and/ormore complex SQL?Thanks in advance!Sam

View 1 Replies View Related

To Use Multiple Value Paramater In The IN List Of A Where Clause

Jan 8, 2008

I have a parent and child package. i pass a parent package variable called @abc with a value of (1,2,3,4,5,6) to the child package. here in the oledb source i have a select statement like,
select *
from A
where id in (@abc)

It gives me an error. any way to make this work.

View 14 Replies View Related

Copyrights 2005-15, All rights reserved