Question about SELECT

Suppose i create sp
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND
column2 = @.criteria2
GO
my question is if parameter @.criteria2 is empty (equal '')
i want the select will ignore this criteria
so it perform like:
SELECT * FROM myTable
WHERE column1 = @.criteria1
the same with other criterias
Message posted via http://www.webservertalk.comE B via webservertalk.com wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
>
WHERE
(column1 = @.criteria1 OR @.criteria1 ='')
HTH,
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Simply try this:
CREATE PROC myProc @.criteria1 int,
@.criteria2 nvarchar(50) = ''
AS
Begin
If @.criteria2 = ''
SELECT * FROM myTable
WHERE column1 = @.criteria1
Else
SELECT * FROM myTable
WHERE column1 = @.criteria1
AND column2 = @.criteria2
End
Gert-Jan
"E B via webservertalk.com" wrote:
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||however if i have suppose 20 paramaters, so i need to write a lot of SELECT
...., because it's a lot of combination.
Any ideas?
Message posted via http://www.webservertalk.com|||This should do. However, for some reason, this query runs very slowly.
SELECT *
FROM myTable
WHERE column1 = @.criteria1
AND column2 = ISNULL(@.criteria2,Column2)
Gopi
"E B via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:912aa0dbc4fb4c68b84db0a061654c2d@.SQ
webservertalk.com...
> Suppose i create sp
> CREATE PROC myProc @.criteria1 int,
> @.criteria2 nvarchar(50) = ''
> AS
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> AND
> column2 = @.criteria2
> GO
> --
> my question is if parameter @.criteria2 is empty (equal '')
> i want the select will ignore this criteria
> so it perform like:
> SELECT * FROM myTable
> WHERE column1 = @.criteria1
> --
> the same with other criterias
> --
> Message posted via http://www.webservertalk.com|||Thanks u. u r the best
Message posted via http://www.webservertalk.com

question about lowercase in sql

Hi,
the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
case).
I did this sql statement:
select myfield from mytable where myfield='MYVALUE'
and .. i got the record.
May i conclude that SQL does make no difference between 'MYVALUE' and
'myvalue'?
Thanks
BobOn Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
> Hi,
> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
> case).
> I did this sql statement:
> select myfield from mytable where myfield='MYVALUE'
> and .. i got the record.
> May i conclude that SQL does make no difference between 'MYVALUE' and
> 'myvalue'?
> Thanks
> Bob
The answer is yes. SQL server is not case sensitive.|||Thanks
"SB" <othellomy@.yahoo.com> schreef in bericht
news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
> The answer is yes. SQL server is not case sensitive.
>|||This answer isn't necessarily true. The column collation is used which
determines case sensitivity. It can be overridden at the TSQL statement level
using COLLATE, but if this is not there, you have to refer to the column's
collation.
HTH,
Paul Ibison|||Hello Bob,
I just want to prevent a possible misunderstanding.
SQL Server is not always "case insensetive". This is about the collation
settings that you set when you setup SQL Server.
If you selected "Case Sensetive" when you set it up, then your current query
would return nothing.
Please read below document from BOL for more information about this setting:
http://msdn2.microsoft.com/en-us/library/ms143508.aspx
--
Ekrem Önsoy
"Bob" <no@.spam.ur> wrote in message
news:OpBGlTS8HHA.484@.TK2MSFTNGP06.phx.gbl...
> Thanks
> "SB" <othellomy@.yahoo.com> schreef in bericht
> news:1189151637.431898.75760@.o80g2000hse.googlegroups.com...
>> On Sep 7, 1:41 pm, "Bob" <n...@.spam.ur> wrote:
>> Hi,
>> the nvarchar field "myfield" contains the stringvalue 'myvalue' (in
>> lower
>> case).
>> I did this sql statement:
>> select myfield from mytable where myfield='MYVALUE'
>> and .. i got the record.
>> May i conclude that SQL does make no difference between 'MYVALUE' and
>> 'myvalue'?
>> Thanks
>> Bob
>> The answer is yes. SQL server is not case sensitive.
>|||Respectfully, this is also only partially true. The collation setting chosen
during the installation becomes the default for the new databases, but it may
be overridden during database creation (or afterwards). Likewise, the
database collation becomes the default for new text-based columns, but this
may also be overridden. The column-level collation is the one being used in
the query, provided that the keyword COLLATE isn't used.
Cheers,
Paul Ibison|||Thanks for addition ;)
--
Ekrem Ã?nsoy
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:A8FE3DCB-F05A-4A4A-B76C-D8CBE3C0C957@.microsoft.com...
> Respectfully, this is also only partially true. The collation setting
> chosen
> during the installation becomes the default for the new databases, but it
> may
> be overridden during database creation (or afterwards). Likewise, the
> database collation becomes the default for new text-based columns, but
> this
> may also be overridden. The column-level collation is the one being used
> in
> the query, provided that the keyword COLLATE isn't used.
> Cheers,
> Paul Ibison
>sql

Question about LIKE and wildcard operator (%)

Hi all,
Given the following code:
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
Why do all 3 statements match successfully?
I only want Test1 and Test2 to match and Test3 to fail...
My end code will be something like (pardon the pun):
SELECT * FROM Table T
WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
without the square brackets.
Regards,
Alextry now ;-)
DECLARE @.param nvarchar(100)
SELECT @.param = '[STRING1][STRING2]'
SELECT
CASE WHEN @.param LIKE '%[[STRING1]]%' THEN 'yes' ELSE 'no' END AS
Test1,
CASE WHEN @.param LIKE '%[[STRING2]]%' THEN 'yes' ELSE 'no' END AS
Test2,
CASE WHEN @.param LIKE '%[[STRING3]]%' THEN 'yes' ELSE 'no' END AS
Test3
you have to escape the brackets
http://sqlservercode.blogspot.com/|||Right... nevermind!!
This is one of those D'oh!!! moments (banging head on the desk as I type
this).
I just realised that [] are used for wildcard character matching...
Changed it to using {} and it's all fine now.
Alex
"Alex" <nospam@.hotmail.com> wrote in message
news:OoPHLqRSGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> Given the following code:
> DECLARE @.param nvarchar(100)
> SELECT @.param = '[STRING1][STRING2]'
> SELECT
> CASE WHEN @.param LIKE '%[STRING1]%' THEN 'yes' ELSE 'no' END AS Test1,
> CASE WHEN @.param LIKE '%[STRING2]%' THEN 'yes' ELSE 'no' END AS Test2,
> CASE WHEN @.param LIKE '%[STRING3]%' THEN 'yes' ELSE 'no' END AS Test3
> Why do all 3 statements match successfully?
> I only want Test1 and Test2 to match and Test3 to fail...
> My end code will be something like (pardon the pun):
> SELECT * FROM Table T
> WHERE @.param LIKE '%[' + T.ColumnToTest + ']%'
> ColumnToTest would contain values such as 'STRING1', 'STRING2', etc...
> without the square brackets.
> Regards,
> Alex
>
>|||Because square brackets are delimiters in wildcard searches; they
indicate that the search "Matches any single character within the
specified range or set that is specified inside the square brackets.".
So in your test, you are asking the optimizer if @.param contains any of
the characters S, T, R, I, N, G, (1-3) which, of course, meets all
conditions of your tests.
You'll either need to ESCAPE the square brackets, or don't use them.
Stu

question about like

I have a table that looks like this

tbl1:
nvchValue nvarchar(100) NOT NULL
biVal1 bigint NOT NULL
biVal2 bigint NOT NULL

If I run this query against it:
SELECT nvchValue
FROM tbl1
WHERE nvchValue LIKE '%<%'

I get a result set that doesn't make any sense to me:
Term

--
?
?
?
fr?it

Those values are in the database, but I don't understand how the match
is working. Any help would be appriciated.It has to do with the collation you are using. Just like an 'a' is
considered equal to an 'A' in a collation that is not case sensitive,
the '<' is apparently considered equal to some of your ? characters.

If you explicitely mention Binary Collation in the WHERE clause, then
you will only get exact matches.

Gert-Jan

alternative_to_what@.yahoo.com wrote:
> I have a table that looks like this
> tbl1:
> nvchValue nvarchar(100) NOT NULL
> biVal1 bigint NOT NULL
> biVal2 bigint NOT NULL
> If I run this query against it:
> SELECT nvchValue
> FROM tbl1
> WHERE nvchValue LIKE '%<%'
> I get a result set that doesn't make any sense to me:
> Term
> --
> ?
> ?
> ?
> fr?it
> Those values are in the database, but I don't understand how the match
> is working. Any help would be appriciated.