question about query part of a string

I'm passing a variable to SQL and I want it to query a column (IP_user), but query any part of what is given. For example if I given Chris it would bring up Chris, Christian, Christine, etc. What is the syntax to do this? thanks

Try this

ALTER PROCEDURE [dbo].[SPU_XXXX]

@.SEARCH VARCHAR(30) = NULL

AS

SET @.SEARCH = '%'+ @.SEARCH + '%'

SELECT CLIENT.CLIENT, CLIENT.CLIENTID

FROM CLIENT

WHERE (CLIENT.CLIENT LIKE @.SEARCH)

|||

The best reference for this question is here:

http://www.sommarskog.se/arrays-in-sql.html

The Table of Numbers method is the easiest, fastest, etc. It will put the data into a table format that you can join to your table in a very straightforward fashion.

http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum

|||

Code Snippet

Set @.search = 'Chris'

--To get all names start with Chris

--i.e., Chrisxxxxxxxx

select * from yourtable where ip_user like @.search + '%'

--To get all names ends with Chris

--i.e., xxxxxChris

select * from yourtable where ip_user like '%' + @.search

--To get all names containes Chris at anyplace

--i.e., xxxxxxChrisxxxxxxxx or xxxxxChris or Chrisxxxxxxxx

select * from yourtable where ip_user like '%' + @.search + '%'

|||

You really should look into Fulltext, as that is its strong feature. Another option using sql engine is to use charindex() function.

e.g.

select *

from tb

where charindex(@.var,your_column)>0

|||

Thank you that was very helpful and it worked great. I have another quick question, and although this isn't technically an access forum it involves access. I have someone who runs access and with an odbc connection, connects to the sql server database. Is there any sql that can be used in accesses queries to replicate this? I apologize ahead of time for posting this here, I couldn't find an access forum...

|||In Access, mark the Query as a 'Pass Though' query, and the query as above will be sent to execute on SQL Server without Access attempting to check syntax and compile.

question about designing tables

Hi,

I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:

[prodID][qty][price]

That is to attch a collection of products to one order entry.

Thank you,
shlomi711Then you should create a table that looks like this:

(RowID Integer Identity) - Not really necessary, but I like to have unique row number
OrderID Integer - The order number (to which the details are connected)
prodID Integer - The product ID
qty money - the amount of products
price money - the price of the products.

Regards
Fredrik

question

is it possible to use table variable in a cursor?
what else are limited in cursor?Why do you think you need cursors?
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:%23Xr1ZceyFHA.2064@.TK2MSFTNGP09.phx.gbl...
> is it possible to use table variable in a cursor?
> what else are limited in cursor?
>
>|||Aaron, just a question, I got this question from an interview.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uxoaTdeyFHA.460@.TK2MSFTNGP15.phx.gbl...
> Why do you think you need cursors?
>
> "Kevin" <pearl_77@.hotmail.com> wrote in message
> news:%23Xr1ZceyFHA.2064@.TK2MSFTNGP09.phx.gbl...
>|||Possible? Yes. Desirable? No.
The best way is usually to avoid cursors.
David Portas
SQL Server MVP
--|||Ah ha, the old "What are the advantages of cursors?" trick question.
Don't fall for that one. ;-)
David Portas
SQL Server MVP
--|||how many types of cursors are there in sql 2000?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128542718.092121.232930@.g14g2000cwa.googlegroups.com...
> Possible? Yes. Desirable? No.
> The best way is usually to avoid cursors.
> --
> David Portas
> SQL Server MVP
> --
>|||Define "Type of cursor". Or, better yet, look up DECLARE CURSOR in Books Onl
ine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kevin" <pearl_77@.hotmail.com> wrote in message news:ekBB6keyFHA.3696@.TK2MSFTNGP10.phx.gbl.
.
> how many types of cursors are there in sql 2000?
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1128542718.092121.232930@.g14g2000cwa.googlegroups.com...
>|||On 5 Oct 2005 13:08:07 -0700, David Portas wrote:

>Ah ha, the old "What are the advantages of cursors?" trick question.
>Don't fall for that one. ;-)
Hi David,
"What are the advantages of cursors?"
"That they can almost always be converted to set-based code."
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Ah ha, the old "What are the advantages of cursors?" trick question.
> Don't fall for that one. ;-)
Well, answer it smartly. They keep set-minded people employed, as with very
little work getting rid of the cursors, they can vastly increase the
performance of any inherited system. ;)|||> "What are the advantages of cursors?"
> "That they can almost always be converted to set-based code."
In other words: "they leave room for improvement, allowing the client to be
charged twice for the same job." :)
ML

Question

In Oracle, you can write a statement that uses the "&" as a variable,

For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.

How do you do this in transact SQL for SQL Server?

Thanks

Lystrau have to pass as parameter in sql server


--eg:

create procedure sample_sp
(
@.Enter_Operator_Name as varchar(100)
)
as

select * from tbl_Stats where operator = @.Enter_Operator_Name|||For example, select * from tbl_Stats where operator = '&Enter_Operator_Name'. The end user will enter an operator_name.
Dunno much about Oracle - but this sounds rather like Access which also is an integrated UI & dbms.

The gist with SQL Server is that the app will have to get the parameter value itself and then pass this to the sproc (a la mallier's answer).|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?|||The procedure was created successfully, however, it did not prompt me to enter an operator name. It is telling me I need to declare my variable @.Enter_Operator_Name.

Any suggestions?

T-SQL is pretty different from Oracle with respect to handling variables. In T-SQL, you are going to end up having to do something like:

DECLARE @.MyVar int

SELECT @.MyVar = 100

SELECT * FROM tblCustomers WHERE CustID = @.MyVar

At runtime, you will have to replace the '= 100' with a value that you supply. Using SQLPlus, I know that you can do things from the command line calling files and replacing variables on the fly with parameters that you supply from the command line. This is doable in SQL Server as well, but you will need to use osql instead of the Query Analyzer to achieve the same effect (and even then I don't know exactly how to do it since I am not an osql guru).

Regards,

hmscott

Edit to add: You said "Procedure". Is this a stored proc you are trying to create? If so, in your stored proce declaration you can do this:

CREATE PROC spGetCustomer (@.CustID int)

AS

SET NOCOUNT ON

SELECT * FROM tblCustomer WHERE CustID = @.CustID|||SQL*Plus is an environment tool as well as a script execution tool. You can compare it to Microsoft Access in that respect, in that both allow easy integration between the UI and the database engine.

A pure database scripting tool can't (and shouldn't) support user interaction. It needs to execute scripts, and only execute scripts. If the data isn't coded into the script, you really need a different tool (such as VBS or Microsoft Access) to properly implement that functionality.

-PatP