Between clause...

Hi all, I need write a stored proc with a clause like "between like xxx AND
like xxx" so, I can't. Thats is, a stored proc that returns values between
two parameters that use wildcards (for instance "LIKE @.date").
Any Ideas. I read all the books and, I encounter information about BETWEEN
usage and LIKE usage, but not how to use BOTH in the same SP.
Thanks an avanceNando_uy wrote:
> Hi all, I need write a stored proc with a clause like "between like xxx AN
D
> like xxx" so, I can't. Thats is, a stored proc that returns values between
> two parameters that use wildcards (for instance "LIKE @.date").
> Any Ideas. I read all the books and, I encounter information about BETWEEN
> usage and LIKE usage, but not how to use BOTH in the same SP.
> Thanks an avance
Depends on what your LIKE clause is and what the desired result is. It
seems to me that "between" two wildcards would only make sense for a
limited number of cases. For example:
CREATE tbl (col VARCHAR(10) NOT NULL, ...);
SELECT col FROM tbl
WHERE col LIKE '[a-c]%';
could be rewritten as:
..
WHERE col >= 'a' AND col < 'd';
but this is obviously different from:
..
WHERE col BETWEEN 'a' AND 'c';
Could you give a better explanation of what you want to achieve please.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can you post the query. Seems very interesting.|||hi all, the query seems like this:
ALTER PROCEDURE dbo.Prueba_Entre
(@.vI nchar(100),
@.vF nchar(100))
AS SELECT Codigo_TSA, Id_Producto, Nombre_Emp, Ingresos, Egresos
FROM dbo.Vista_Prueba
WHERE (Codigo_TSA BETWEEN @.vI AND @.vF)
I want to substitute the parameters @.vI, @.vF for something like this:
LIKE @.vI AND LIKE @.vI, since I need that the parameters accept wildcards (%)
.
I currently use SQL server 2000 and, for simplicity use ACCESS 2003 to
graphically design the SP.
In other words, I need to retrieve values between two parameters, both MUST
accept wildcards, It's possible?
"Omnibuzz" wrote:

> Can you post the query. Seems very interesting.|||Thanks for the input.
But we need to know the functionality behind using the like operator.
Because from my experience, I would say it is logically wrong in any
situation to use like and between in the following cases
(pseudo code, of course :)
col1 between like '%abc%' and like '%xyz%'
of course if you want it to be compared between something like
'%abc' and '%xyz'
or
'abc%' and 'xyz%'
then you can use something like this in the where condition.
left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
left operator in this case
or
right(col1,3) between 'abc' and 'xyz'
Boy.. lack of info leads to lots of typing :)
Hope this helps.|||thanks for you time, the case is: I have a product table, I want to retrieve
a range of these by product code, ie: 10xxxx to 25xxx, because I want the
user have the facility to write ONLY the first characters of product code to
establish the range
I it's possible?
Thanks
"Omnibuzz" wrote:

> Thanks for the input.
> But we need to know the functionality behind using the like operator.
> Because from my experience, I would say it is logically wrong in any
> situation to use like and between in the following cases
> (pseudo code, of course :)
> col1 between like '%abc%' and like '%xyz%'
> of course if you want it to be compared between something like
> '%abc' and '%xyz'
> or
> 'abc%' and 'xyz%'
> then you can use something like this in the where condition.
> left(col1,3) between 'abc' and 'xyz' -- actually you don't even need the
> left operator in this case
> or
> right(col1,3) between 'abc' and 'xyz'
> Boy.. lack of info leads to lots of typing :)
> Hope this helps.
>
>|||Is it an integer column or a char column (or does the column hold integer
values only?).
And do they always enter the first 2 digits? or it can be 1 or 3 digits?
"Nando_uy" wrote:
> thanks for you time, the case is: I have a product table, I want to retrie
ve
> a range of these by product code, ie: 10xxxx to 25xxx, because I want the
> user have the facility to write ONLY the first characters of product code
to
> establish the range
> I it's possible?
> Thanks
> "Omnibuzz" wrote:
>|||thanks Omnibuzz, it's a character column and until now I don't decide the
numbers of characters the user input, but say, 2 characters will be fine!!!
Looks better?
"Omnibuzz" wrote:
> Is it an integer column or a char column (or does the column hold integer
> values only?).
> And do they always enter the first 2 digits? or it can be 1 or 3 digits?
>
> "Nando_uy" wrote:
>|||Anyways.. check this out..
create table #temp( a varchar(10))
insert into #temp
select '12ewew'
union all
select '13rere'
union all
select '23dds'
union all
select '26rerere'
union all
select '2454cdfd'
select * from #temp where a between '12' and '25'
Hope this helps.|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Depends on what your LIKE clause is and what the desired result is. It
> seems to me that "between" two wildcards would only make sense for a
> limited number of cases. For example:
> CREATE tbl (col VARCHAR(10) NOT NULL, ...);
> SELECT col FROM tbl
> WHERE col LIKE '[a-c]%';
> could be rewritten as:
> ...
> WHERE col >= 'a' AND col < 'd';
Maybe, but it would not necessarily generate the same result:
CREATE TABLE #tmp (a varchar(19) COLLATE Finnish_Swedish_CS_AS NOT NULL)
go
INSERT #tmp(a) VALUES ('Annichen')
INSERT #tmp(a) VALUES ('avfallskvarn')
INSERT #tmp(a) VALUES ('Beatrice')
INSERT #tmp(a) VALUES ('betongarbetare')
INSERT #tmp(a) VALUES ('Cecilia')
INSERT #tmp(a) VALUES ('citrusfrukt')
INSERT #tmp(a) VALUES ('Daneiella')
INSERT #tmp(a) VALUES ('daggfuktig')
go
SELECT a FROM #tmp WHERE a LIKE '[a-c]%' -- five rows
SELECT a FROM #tmp WHERE a >= 'a' AND a < 'd' -- six rows
go
DROP TABLE #tmp
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Better way to build a stored proc for an INSERT...

I've built a stored procedure where I'm inserting a row into two tables.
Both tables have a number of columns - and so I have to pass a rather larger
number of parameters to the stored proc. Like follows
INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1, @.cParm2,
... @.cParm25)
INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
@.cParm27, ... @.cParm50)
For any one row, however, at least a third of the columns are going to be
NULL, based on the value of one of the columns (a category column).
There's no opportunity to modify the table structure - it is what it is.
What I have "works", but I'm curious if there's a way that doesn't involve
as many parameters.
Thanks,
KevinKevin@.test.com wrote:
> I've built a stored procedure where I'm inserting a row into two
> tables.
> Both tables have a number of columns - and so I have to pass a rather
> larger number of parameters to the stored proc. Like follows
> INSERT INTO MyTable1 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm1,
> @.cParm2, ... @.cParm25)
> INSERT INTO MyTable2 (MyCol1, MyCol2, ... MyCol25) VALUES (@.cParm26,
> @.cParm27, ... @.cParm50)
>
> For any one row, however, at least a third of the columns are going
> to be NULL, based on the value of one of the columns (a category
> column).
> There's no opportunity to modify the table structure - it is what it
> is. What I have "works", but I'm curious if there's a way that
> doesn't involve as many parameters.
>
> Thanks,
> Kevin
Write separate stored procedures for each "insert" type. So, let's say
your table has three logical implementations (design-issues aside), you
can write three separate insert SPs that only require the user pass
those that are asked.
The other option is to use defaults on the parameters, so if they are
not passed they default to an appropriate value:
Create Proc Test
@.Param1 INT = NULL
@.Param2 INT = NULL
as
Exec dbo.Test @.Param2 = 5
Exec dbo.Test @.Param1 = 3
Exec dbo.Test 1, 3
Exec dbo.Test
You may have to add some validation to the SP in the case where a user
leaves out a logically incorrect number of columns.
David Gugick
Imceda Software
www.imceda.com|||David,
Thanks!...you've given me some good ideas to seriously consider, especially
having 3 stored procs.
Kevin

Best way to tell if a SP is a query Programatically

Using ADO.NET Or regular queries on SQL Server, is there a good way of
determining if a Stored Proc will be returning a result set or if it is
a non-query that does all I/O through parameters?

Thanks.Hi

You should know this when you program your data access routines and a
recordset needs to be declared to hold the resultset. If the parameters are
input/output/result then they need to be declared accordingly. You may look
at the syscolumns table that has a isoutparam column that will determine if
a parameter is an output parameter.

John
<wackyphill@.yahoo.com> wrote in message
news:1126884177.488723.219470@.g43g2000cwa.googlegr oups.com...
> Using ADO.NET Or regular queries on SQL Server, is there a good way of
> determining if a Stored Proc will be returning a result set or if it is
> a non-query that does all I/O through parameters?
> Thanks.