Showing posts with label clause. Show all posts
Showing posts with label clause. Show all posts

Wednesday, March 28, 2012

How to use wildcards in WHERE clause

I have a SQL statement which is generated dynamically. I need to know what is the correct syntax for this

WHERE status = 'open' AND salesman = * AND dat = * AND customername = *

i.e. fetch everything WHERE status = 'open'

I know that simply WHERE status = 'open' would do the trick but I need it like the first example because of the way the statement is being generated i.e. this salesmen bit is like this.

If Salesman <> "*" Then
sql2 &= " AND salesman = '" & Salesman & "'"
Else
sql2 &= " AND salesman = *"
End If

Thanks

BenI presume you are saying that the salesman should be LIKE anything.

You should try:

WHERE status = 'open' AND salesman LIKE '%' AND dat LIKE '%' AND customername LIKE '%'

OleDb uses % as the wildcard character, and you need to use LIKE with wildcards.|||Yes, thats done the trick.

Thanks a lot.

Ben

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

How to use the return of select .... for xml auto...?

There is a lot of limitation on for xml clause. Is it possible to combine
several select statement with for xml to a big xml file?
Basically I want to implement something like:
select ... for xml auto
union all
select ... for xml auto
union all
.....(select ...
union all
select ...)for xml auto
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/

Monday, March 12, 2012

How to use multle values for Where clause

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.

This is how you have to do it

SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)

|||Try this... SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID in (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)|||

There are 2 ways.

SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123
Hope this will help.|||

Thanks.

By Changing the = to IN, it worked perfectly.

How to Use Login-Less Users the Alternate to Application Roles?

SQL Server 2005 introduces a new type of database user, one that is created using the “WITHOUT LOGIN” clause. This user is not mapped to a login, and is being touted as an alternate to Application Roles because no password is needed.. What I do not understand is how to use this type of user. I know how to use sp_setapprole with Application Roles, what are the equivalent steps needed to use login-less users?

Thank you in advance.

Terry Duffy

As I recall, the [WITHOUT LOGIN] option allows a user into the server without a specific database login. That user is mapped to the GUEST account.

In my opinion, you never want to use the GUEST account, so I couldn't recommend exploring this any further. (But someone must have a good case for this option.)

|||

You can impersonate loginless principals (using EXECUTE AS USER/EXECUTE AS on a module) the same way you can impersonate regular SQL users.

The only difference is that, based on the SQL USER SID, the system can recognize that these principals don’t have any login mapped to them. It will create a low privileged login token for impersonation, and a full user token for the database.

If you want to use them in a similar fashion as application roles, you can use the WITH NO REVERT or WITH COOKIE clauses in EXECUTE AS (ad hoc). http://msdn2.microsoft.com/en-us/library/ms181362.aspx

You can use them as an application context (using EXECUTE AS in your SP/UDF definition) and grant permissions only to this loginless user. This way you will only need to grant permission to execute your application SP/UDF to other users instead of granting direct permissions on the resources.

I also talk about these new principals on my blog: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

Let us know if you have any further questions or feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Arnie, users without login are only mapped to guest if they switch context to another database than the one they're defined in - because guest is by default disabled in user databases, this means that users without login are restricted from going to other user databases. You don't have to use the guest account, to use a user without login - such user is useful by itself. This mapping to guest is actually the only significant difference between users without login and approles.

Thanks

Laurentiu

|||

Thank you all. Just yesterday I attended a MS security web cast where login less users were highly recommended, so I do intend to research this further. Where application roles require the use of a password, this new type of user does not. Raul, your blog was cited as a resource and I was familiar with your content.

If I am reading this correctly, the only way to use this type of user is with "EXECUTE AS", and there is no comparable functionality to sp_setapprole. Is this correct? If true, I just don't see any advantage to this type of user over using "EXECUTE AS" with a user with a login, and APPLICATION ROLES (despite the password) seem more secure.

Thanks again to all.

Terry

|||

Both EXECUTE AS (stand alone) and sp_setapprole impersonation mechanisms can be used in similar ways. Both have options to prevent reverting back to the original context and both have an optional cookie-based mechanism to revert to the original context.The main difference between approles & user without login is the mechanism to verify the right to impersonate them: Approles are based on passwords and loginless users are based on permissions.

While using password as a protection mechanism can be effective, from the management point of view, requiring a password for implies that the security relies on keeping the password safe and having good password policies around them.The most common pitfall when using approles is hardcoding the password in scripts and applications. I have seen this mal-practice multiple times, and besides the obvious problem that it only takes to find the approle once to compromise the security, there is no easy way to comply with password policies and change the password regularly.If an attacker knows the password for an approle the only way to keep her from abusing the approle is to change the password to something different (affecting also regular applications and users who will need to get the new password). For loginless users, you only need to revoke/deny permission to impersonate such user o the attacker.

If you decide to use approles (a valid option, as Microsoft fully supports the feature in SQL Server 2005) I strongly recommend having a strategy to avoid hardcoding the password in any script/application and have password policies (especially password maximum life-time) in mind.

We appreciate any comments and suggestions on how we can improve our security features. If you have any concerns or feedback regarding loginless users or approles, please let us know.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry Duffy
You use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:

>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry DuffyYou use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archiv.../03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:

>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

How to Use Login-Less Users the Alternate to Application Roles

SQL Server 2005 introduces a new type of database user, one that is
created using the "WITHOUT LOGIN" clause. This user is not mapped to a
login, and is being touted as an alternate to Application Roles
because no password is needed.. What I do not understand is how to use
this type of user. I know how to use sp_setapprole with Application
Roles, what are the equivalent steps needed to use login-less users?
Thank you in advance.
Terry DuffyYou use it with EXECUTE AS. This blog entry has a demo
walking through how to use similar to an application role.
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
-Sue
On 12 Apr 2007 14:44:27 -0700, "Terry" <tduffy@.calamos.com>
wrote:
>SQL Server 2005 introduces a new type of database user, one that is
>created using the "WITHOUT LOGIN" clause. This user is not mapped to a
>login, and is being touted as an alternate to Application Roles
>because no password is needed.. What I do not understand is how to use
>this type of user. I know how to use sp_setapprole with Application
>Roles, what are the equivalent steps needed to use login-less users?
>Thank you in advance.
>Terry Duffy

Friday, March 9, 2012

How to use IIF function (or similar syntax) in SELECT clause

I need to create a simple view containing a driverID that must be converted
to numeric.
IF isnumeric(driverID) = 0 then 0
I tried to use IIF(isnumeric(driverID), driver, 0) but it's not accepted in
regular query.
Please help!
Thanks
BillYou can use a CASE expression; For example:
SELECT
CASE WHEN ISNUMERIC(DriverID) = 1 THEN DriverID ELSE 0 END
FROM MyTable
Note that ISNUMERIC can return 1 for values you might not want to consider
as numeric (e.g. '1E0') so you might want to substitute LIKE:
SELECT
CASE WHEN DriverID NOT LIKE '%[^0-9]%' THEN DriverID ELSE 0 END
FROM MyTable
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:ekpb7A%231FHA.3660@.TK2MSFTNGP15.phx.gbl...
>I need to create a simple view containing a driverID that must be converted
>to numeric.
> IF isnumeric(driverID) = 0 then 0
> I tried to use IIF(isnumeric(driverID), driver, 0) but it's not accepted
> in regular query.
> Please help!
> Thanks
> Bill
>

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

Sunday, February 19, 2012

How to use a SP parameter in a where in clause?

I am trying to do a 'WHERE IN' clause by taking the value of a paremeter and using it as the value in the WHERE IN clause such as:

DECLARE @.parm NVARCHAR(1000) = 'IBM, MOT, MSFT'

SELECT * FROM SomeStockTable WHERE TickerSymbol IN (@.parm)

I would like to avoid using an EXEC statement and string-building this whole query, since that is ugle and hard to maintain.

ANy ideas?

TYVM

See the discussion on tmorton's fnSplit:

http://forums.asp.net/304221/ShowPost.aspx

although she recommends using it as part of a join, I'm lazy. I normally do somehting like this (Which should turn out to be fairly equivalent, but easier for me to read):

SELECT *

FROM sometable

WHERE ID IN (SELECT ID FROM dbo.Split(@.param,DEFAULT))

-- My Split function, which is similair to tmorton's has only one field named id, and the default for the second parameter is a comma. I have found the performance to be more than fine, even on very large and complex queries. Some of which tie together 10+ tables, of which have 10 million+ rows.