473,882 Members | 1,628 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

select * from table where column = @AllValues?

Hello,
I am passing a value to a stored procedure in vb.net to get normally a
selection of the table. How could I get the complete table without
writing the code twice? Many thanks in advance ....

Sep 5 '05 #1
9 8011
Am 5 Sep 2005 14:01:33 -0700 schrieb sa*******@gmx.n et:
Hello,
I am passing a value to a stored procedure in vb.net to get normally a
selection of the table. How could I get the complete table without
writing the code twice? Many thanks in advance ....


You need a second parameter to enable/disable the where criteria. Your
select would look like so:

select * from table where column = @param1 or @param2 = 1

if you use @param2 = 0 then the column is the criteria, if you use @param2
= 1 then you will always get all rows, no matter what's in param1.

bye,
Helmut
Sep 6 '05 #2
Dear Helmut,
thank you very much - job done....

;-)
Satunius

Sep 6 '05 #3
Pass a NULL as you flag for all rows. :

SELECT <list of columns>
FROM Foobar
WHERE some_col = COALESCE( @parm, some_col);

Sep 6 '05 #4
AK
if performance is importnatn in this case, I'd write 2 different select
statements

Sep 6 '05 #5

--CELKO-- wrote:
Pass a NULL as you flag for all rows. :

SELECT <list of columns>
FROM Foobar
WHERE some_col = COALESCE( @parm, some_col);


What if some_col is DATETIME and NULLable?

Thanks.

Sep 9 '05 #6


declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column=' + @AllValues

exec (@Sql)
Rakesh

*** Sent via Developersdex http://www.developersdex.com ***
Sep 9 '05 #7
Sorry, I forgot to complete my code.

In SP u can use

declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column IN (' + @AllValues + ') '

exec (@Sql)
Rakesh

*** Sent via Developersdex http://www.developersdex.com ***
Sep 9 '05 #8


Misunderstood ur question
some_col = COALESCE( @parm, some_col)

or

@parm IS NULL OR some_col = @parm

*** Sent via Developersdex http://www.developersdex.com ***
Sep 9 '05 #9
Rakesh Makhijani (ra************ ***@yahoo.com) writes:
Sorry, I forgot to complete my code.

In SP u can use

declare @Sql varchar(8000),
@AllValues varchar(1000)

SELECT @Sql='select * from table where column IN (' + @AllValues + ') '

exec (@Sql)


Not only this was appearenly the answer to the wrong question, it was
the wrong answer to that question. Do *not* use dynamic SQL for
for testing for a list of values. There are a number of ways to unpack
such a list into table, and they are both without security issues, and
have better performance. Please see
http://www.sommarskog.se/arrays-in-sql.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

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

Sep 9 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
17362
by: Martin Lucas-Smith | last post by:
Can anyone point me to a regular expression in PHP which could be used to check that a proposed (My)SQL database/table/column name is valid, i.e. shouldn't result in an SQL error when created? The user of my (hopefully to be opensourced) program has the ability to create database/table/column names on the fly. I'm aware of obvious characters such as ., , things like >, etc., which won't work, but haven't been able to source a...
1
3262
by: chelleybabyger | last post by:
Using the below method, <% remaining_stock = cint(product_stock) - cint(chQuantity) %> <% sqlString = "UPDATE Products SET " &_ "product_stock='" & remaining_stock & "'," &_ "WHERE product_id='" &chID& "'"
3
2532
by: Albretch | last post by:
I am trying to insert some textual data belonging to an HTML page into a table column with 'TEXT' as data type mysql's maual _/manual.html#String_types tell you, you may insert up to (2^16 - 1), that is 65535 characters, but I am getting errors when I try to insert a column larger than 236 characters. Initially I thought it might be because I had to escape some characters, but after playing a some scenarios and just inserting a bunch...
1
5895
by: VMI | last post by:
How can I sort a table column correctly when the table has values like "0", "A1", "AA-1", "B21", "3C", 4-32A", "1", "11-1", 2-A", etc... The table will then be loaded to a grid and that's when it'll have to sort correctly (when clicking on the col. header). Windows Explorer (in XP) seems to do this correctly when I sort files with these names. In the column, I tried padding all values with 0's and the sorting improved but it still...
4
2995
by: Scot L. Harris | last post by:
Currently using Postgresql 7.2.4-5.80 with php 4.2.2.-8.0.8 on a redhat 8.0 system. I am writing some php scripts where I want to generate a list of the column names in a particular table that the user selects. I could take the brute force method and hard code the column names but then every time I add a new table or modify an existing one I would have to modify the code. What I want is to have a generic function that given the table...
3
1612
bartonc
by: bartonc | last post by:
Is it possible to select a column of type INTEGER PRIMARY KEY without knowing its name? This also happens to be column zero in all my tables. I've played with lots of different syntaxes, to no avail. I was hoping for something compatible with SQLite like:SELECT * FROM tablename WHERE ROWID = n; # or SELECT * FROM tablename WHERE _ROWID_ = n; # or SELECT * FROM tablename WHERE OID = n;Thanks, all.
8
6794
by: mguy27 | last post by:
We have about 2 dozen security officers who patrol about 120 buildings. They find defective or unlocked doors and write a "Trouble Report" on the door. This card gets turned in, where I have designed an Access Field and Table to enter the information and store it. They then get emailed to their respective supervisors so the problems can be dealt with. As of right now, we have a big list on the wall of the buildings and their supervisors....
8
13023
by: gigonomics | last post by:
Hi all, I hope someone can help me out. I need to return the best available seats subject to the constraint that the seats are side by side (or return X consecutive records from a table column where the values are integers). I can do this programmatically (using code and stored procedures), but it's not a neat solution and there are also performance issues. Returning the best available X number of seats is very straightforward. But I...
3
3499
by: jagdeep gupta | last post by:
Here i use image in background in table column it does not displays the image but if i apply the same path for img tag in foreground it displays. Here is code: <%@ Master Language="C#" AutoEventWireup="true" CodeFile="MasterPage.master.cs" Inherits="MasterPage" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head...
0
9931
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9777
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11106
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10829
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9556
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7955
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7113
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5781
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5978
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.