"Macro" statement

Is it possible to write a macro statement using Transact-SQL?
Imagine that we have a table named tblA and fields with the almost
same name, for example Field01, Field02,...Field20. (I have named
fields on that way for the better explanation).
Now, suppose that we want to do almost the same update on all of the
fields:
UPDATE tblA
SET Field01 = 100000
UPDATE tblA
SET Field02 = 100000
and so on...
(we must write 20 identical statements). This example is very simple
(please, forget the solution with one statement because it is clear!).
I wrote a simple example because of my next explanation and question.
In some other languages it is not necessarily to write 20 almost
identical statements. I can write something like this:
FOR i: = 1 TO 20
cTemp := CHAR2(i)
REPLACE Field&cTemp with 10000
NEXT i
-- cTemp (using CHAR2 convert function) have a character values: '01',
'02', '03'... etc.
As you can see, with every step through the loop I have changed the
statements using macro Field&cTemp.
Is it possible to write a similar solution in Transact SQL and avoid
20 identical statements?DECLARE @.i int, @.qry varchar(500)
SET @.i=1
WHILE @.i<=20 BEGIN
SET @.Qry='UPDATE tblA SET Field'+Cast(@.i as varchar)+'=100000'
EXEC(@.Qry)
SET @.i=@.i+1
END
This is not the most efficient method but it closely follows your
example(minus the 0 prefix on the first 9 fields). Better would be to build
up the string for a single update to all columns but I'll leave that to you
:)
Mr Tea
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||A couple of questions:
1. Is there a where clause, or is this a single row table?
2. Updating the same row or rows twenty different times is not a very
efficient approach (it will end up taking twenty different log writes!)
3. How are you matching the field with the value?
In general it is far better when it comes to SQL to execute fewer complex
statements than many simpler statements. Building the proper statement and
executing it will be far better. So you could write something like:
--not meant to be compilable, pseudocode only
set @.query = 'UPDATE tblA --hopefully not your real table name'
set @.query = 'SET '
set @.i = 1
while @.i < 20
begin
set @.query = @.query + 'Field' + cast(@.i as varchar(2)) + ' = 100000, '
set @.i = @.i + 1
end
set @.query = @.query + 'WHERE --and your where clause'
exec (@.query)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"zaratino" <goran.abdic@.zg.htnet.hr> wrote in message
news:b85bv0dq2h7rqf1atoecb7v71cke66aemu@.
4ax.com...
> Is it possible to write a macro statement using Transact-SQL?
> Imagine that we have a table named tblA and fields with the almost
> same name, for example Field01, Field02,...Field20. (I have named
> fields on that way for the better explanation).
> Now, suppose that we want to do almost the same update on all of the
> fields:
> UPDATE tblA
> SET Field01 = 100000
> UPDATE tblA
> SET Field02 = 100000
> and so on...
> (we must write 20 identical statements). This example is very simple
> (please, forget the solution with one statement because it is clear!).
> I wrote a simple example because of my next explanation and question.
> In some other languages it is not necessarily to write 20 almost
> identical statements. I can write something like this:
> FOR i: = 1 TO 20
> cTemp := CHAR2(i)
> REPLACE Field&cTemp with 10000
> NEXT i
> -- cTemp (using CHAR2 convert function) have a character values: '01',
> '02', '03'... etc.
> As you can see, with every step through the loop I have changed the
> statements using macro Field&cTemp.
> Is it possible to write a similar solution in Transact SQL and avoid
> 20 identical statements?|||Thanks a lot Mr Tea and Mr Davidson
It's work (on my more complex task). :-)
And of course - answers:
1. I have a another table (not this one for update). That table
contains circular nodes of hieararchy. This is the reason why I must
first fullfill columns step-by-step.
2. ...it means: yes, I have a WHERE clause and FROM clause (JOIN with
circular table) also.
3. Yes, .log file is written 20 times but I will sucrifise that. I
work with basic data (corporate hierarchy) - not huge set of rows.
4. Of course, matching values in my example is not so simple. I
matching a values with another one 'macro' that read a data form
another table.
...and all of that because you help me! :-)
So,
Thank you once again
Mr Zaratino|||>> Imagine that we have a table named tblA and fields [sic] with the
almost same name, for example Field01, Field02,...Field20. (I have
named fields [sic] on that way for the better explanation). <<
A column is not a field -- nothing like it at all. Since each column
is a separate attribute of the entity in your data model, it would be
VERY unusual to have such a table if you had a proper data model.
However, if I were writing a 1950's file system (files are made of
records which do have fields), then they would probably be a repeating
group -- and a violation of First Normal Form (1NF).
fields [sic]: <<
In SQL an UPDATE works on entire rows (rows are not records), changing
all the columns at the same time.
UPDATE Foobar
SET x = <value1>,
y = <value2>,
z = <value3>,
etc.
If you want to pass the values as parameters, then you can skip some of
them by passing a NULL and having this SET clause in your UPDATE
statement.
SET x = COALESCE (<value1>, x)
Dynamic SQL generation is considered very poor design; it says you have
no data model and no idea what to do until run time.|||>> That table contains circular nodes of hieararchy. This is the reason
why I must
first fullfill columns step-by-step. <<
Do you mean that you are using an adjacency list model for a hierarchy?
If so, look up the nested set model instead. Otherwise, you are not
usingthe power of a set-oriented language and have re-invented a file
system.|||Yes, Celko - everything that you said is correct, I understand UPDATE
statement; sorry for my confusion about 'fields' and 'columns'.
My congratulation, you recognize that I violate 1NF but there is a
good reason for that. I need that look of table for further purpose
(cube). With table like this the next actions are faster...(sometimes
this is even necessarly).
Thanks,
Zaratino

"GO" statement in SQK2K

Hi,
I have been using a lot of "GO" statement in my SQL
script (say a file called abc.sql) with SQL7.0
For Example :
--
Select * from A
GO
Update A Set COL1 = Null
GO
Since I upgraded to SQL2K, I have go a SYNTAX
error.
Help Needed
Thanks
EJChewFrom where do you execute the SQL code? QA? Check configuration if someone changed the batch
separator. Can you post the exact errormessage?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in message news:3f94db83@.news.starhub.net.sg...
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>|||ejchew:
just a stab in the dark, but do you have the GO statments located on the
same line as another sql statement? this will result in an error
for example:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects; GO
now try the following queries:
USE master
GO
SELECT name FROM dbo.sysobjects;
GO
SELECT id FROM dbo.sysobjects;
GO
hth
jeff clausius
sourcegear corporation
"EJChew" <ejchew@.cyberoffice.com.sg> wrote in news:3f94db83
@.news.starhub.net.sg:
> Hi,
> I have been using a lot of "GO" statement in my SQL
> script (say a file called abc.sql) with SQL7.0
> For Example :
> --
> Select * from A
> GO
> Update A Set COL1 = Null
> GO
> Since I upgraded to SQL2K, I have go a SYNTAX
> error.
> Help Needed
> Thanks
> EJChew
>
>

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?
Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>
|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> and
> against
> Enterprise
> do
>
|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
http://www.aspfaq.com/
(Reverse address to reply.)
|||
> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.
|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.

> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
http://www.aspfaq.com/
(Reverse address to reply.)

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
>> Within Sql Server Enterprise, I click on an object to show dependencies,
> and
>> there is something I am not seeing here.
>> For example, I want to see all dependencies for a given table. It shows
>> a
>> view and a couple of stored procedures that perform SELECT statements.
>> However, there is a stored procedure that performs UPDATE and INSERT
> against
>> that particular table, but I am not seeing on in the Display Dependencies
>> screen.
>> When I attempt to see the Dependencies screen on the stored procedure
>> that
>> does UPDATE and INSERT, I am not seeing the table listed.
>> There seems to be a disconnect some place either in my head or in
> Enterprise
>> Manager. What am I missing? How can I display, all, I mean ALL
>> dependencies? For a given table, I want stored procedures listed if they
> do
>> ANYTHING on the table. And views. And functions. All dependencies,
>> period. How can I do that?
>>
>|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.
> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
--
http://www.aspfaq.com/
(Reverse address to reply.)

"did you mean" feature

Having a list of words in a table...haw can I make with SQL a "did you
mean" search?
for example..
Having a table with these data:
hello
hallo
hi
lup
hai
If I look for "hollo" it should return "hello", "hallo"
I know this could be a very complex algorithm, but I'm looking for it's
simplest form that could be implemented with Transact SQL
Best Regards
Fabio Cavassini
http://www.pldsa.comHave a look at the thesaurus feature in SQL FTS. This is supported in SQL
2005, and unsupported but implemented for FreeText search in SQL 2000.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137181586.850037.17240@.g43g2000cwa.googlegroups.com...
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for it's
> simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
>|||Are saying that you want to select all rows where key values resemble a
given parameter?
There is the LIKE operator for performing truncated, wildcard, or
substitution type comparisons. For example:
select * from words where word like 'hel%'
select * from words where work like 'h_ll_'
There is also the SoundEx() function which accepts a character string and
returns a condensed code based on the string's phoenic spelling. For
example:
select * from words where word = SoundEx('hello')
print soundex('hello')
H400
print soundex('hallo')
H400
print soundex('half')
H410
This is also useful when searching on first or last names.
The most accurate solution would be to have a referece table which maps each
word to 0 - many equivalent words.
select word from Thesaurus where synonym = 'hello'
"Fabio Cavassini" <cavassinif@.gmail.com> wrote in message
news:1137181586.850037.17240@.g43g2000cwa.googlegroups.com...
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for it's
> simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
>|||Fabio Cavassini wrote:
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for
> it's simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
Have a look at the soundex function, and consider storing the soudnex values
in the table to avoid table scan operations each time. Best to do this from
the stored procedure that does the inserting:
create table SoundexTest (Col1 varchar(30), Col1Soundex char(30))
create index Col1SoundexIDX on SoundexTest (Col1Soundex)
Insert into SoundexTest Values ('Hello', SOUNDEX('Hello'))
Insert into SoundexTest Values ('Hallo', SOUNDEX('Hallo'))
Insert into SoundexTest Values ('Hey', SOUNDEX('Hey'))
Select * from SoundexTest where Col1Soundex = SOUNDEX('Hallo')
drop table SoundexTest
David Gugick
Quest Software|||Fabio Cavassini wrote:
> Having a list of words in a table...haw can I make with SQL a "did you
> mean" search?
> for example..
> Having a table with these data:
> hello
> hallo
> hi
> lup
> hai
> If I look for "hollo" it should return "hello", "hallo"
> I know this could be a very complex algorithm, but I'm looking for
> it's simplest form that could be implemented with Transact SQL
> Best Regards
> Fabio Cavassini
> http://www.pldsa.com
As well as this:
http://techrepublic.com.com/5102-9592-5716625.html
David Gugick
Quest Software|||This will work for you
it will be expensive (table scan etc) but it's the simplest form
you could put it in a UDF
add i, u and y for the complete set
create table testString (value varchar(55))
insert into testString
select 'hello' union all
select 'hallo' union all
select 'hi' union all
select 'lup' union all
select 'hai'
declare @.Value varchar(55)
select @.value = 'hollo'
select * from testString
where replace(replace(replace(value,'e',''),'a
',''),'o','') =
replace(replace(replace(@.value,'e',''),'
a',''),'o','')
http://sqlservercode.blogspot.com/|||> As well as this:
> http://techrepublic.com.com/5102-9592-5716625.html
and where are Listing A and Listing B ?
regards, Robert|||Robert Fuchs wrote:
> and where are Listing A and Listing B ?
> regards, Robert
http://techrepublic.com.com/5100-95...html?tag=search
David Gugick
Quest Software
www.quest.com|||>> know this could be a very complex algorithm <<
OH YES!! Do not do this kind of search in SQL; use a textbase tool
which has the complex algorithms tuned for a particlar natural
language, like English.|||What like the Full-Text search facilities that are already built into SQL
Server and allow you to join returned documents from your text search
against your schema - extremely powerful i'd say.
Really, have a look at the complete feature list that is offered in SQL
Server, its not just a relational data storage and retrieval engine.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1137736598.084973.118370@.z14g2000cwz.googlegroups.com...
> OH YES!! Do not do this kind of search in SQL; use a textbase tool
> which has the complex algorithms tuned for a particlar natural
> language, like English.
>

"Decode on rowsource for listbox"

Hi.

I have coded the rowsource for my listbox and I need some sort of "decode" function within my SELECT statement. Can someone help?

For example,

Teacher is a checkbox which contains 0 and 1
I need the listbox to show the word "Teacher" if teacher = 1; else show "Assistant"

mylstbox.rowsource = "SELECT Teacher From mytable"

Statement above populates my listbox with 0 or 1. However, I need the word "Teacher" or "Assistant" to show on the listbox.

Help!

Thanks

SHKI found the dolution by using "case when then else"

Comment disables code?

It seem that using the old "--" style comment screws code in the IDE under
certain circumstances. As an example, the code in example one should create
a temporary table, populate it with the current int ID from a table along
with an int IDENTITY column that will serve as the new ID, then display the
resulting temporary table. The code runs as expected without the -- Comment
line, but as soon as it is added the table no longer populates, and the
temporary table is no longer displayed. The same type thing seems to happen
when you place -- comments following values in a SELECT statement as in
example two.
I ran the code under both Query Analyzer and the new 2005 IDE and they both
exhibited thje same behavior, but I've used the -- comment style for years
without an issue so I'm baffled. So far it only seems to affect SELECT
statements.
Has anyone else encountered this?
/* Example One */
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
-- Comment
INSERT tempdb.dbo.LotEntity (OldID)
SELECT EntityID
FROM dbo.tbl_LotEntities
SELECT * FROM tempdb.dbo.LotEntity
/* Example 2 */
INSERT TableTwo (
ColumnOne,
ColumnTwo)
SELECT
ValueOne, -- ColumnOne
ValueTwo
FROM TableTwoI couldn't reproduce either of those problems.
For example 1, the problem probably not the comment, but that you do the SEL
ECT in the INSERT in the
same batch as the table is created. My guess is that the parses gets confuse
d when you in the same
batch creates a table, and then does a SELECT inside an INSERT where both of
these later statements
refer to the same table. That code generated an error (probably from the SEL
ECT inside the INSERT)
regardless of whether I have the comment or not.
For example 1, I got a proper error message stating that the table doesn't e
xist. And if I create
the table, no errors:
DROP TABLE TableTwo
GO
CREATE TABLE TableTwo (ColumnOne int, ColumnTwo int, ValueOne int, ValueTwo
int)
GO
INSERT TableTwo (
ColumnOne,
ColumnTwo)
SELECT
ValueOne, -- ColumnOne
ValueTwo
FROM TableTwo
If you can post a full repro, we can have a look at it. Oh, watch out for ed
itors etc that doesn't
produce a full CRLF. I've seen cases when you have only a CR (or was it LF)
which visually produces
a new line, but doesn't end the comment.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:BF89D3A3-A798-4220-852F-7A5FCC33971E@.microsoft.com...
> It seem that using the old "--" style comment screws code in the IDE under
> certain circumstances. As an example, the code in example one should crea
te
> a temporary table, populate it with the current int ID from a table along
> with an int IDENTITY column that will serve as the new ID, then display th
e
> resulting temporary table. The code runs as expected without the -- Comme
nt
> line, but as soon as it is added the table no longer populates, and the
> temporary table is no longer displayed. The same type thing seems to happ
en
> when you place -- comments following values in a SELECT statement as in
> example two.
> I ran the code under both Query Analyzer and the new 2005 IDE and they bot
h
> exhibited thje same behavior, but I've used the -- comment style for years
> without an issue so I'm baffled. So far it only seems to affect SELECT
> statements.
> Has anyone else encountered this?
>
> /* Example One */
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT EntityID
> FROM dbo.tbl_LotEntities
> SELECT * FROM tempdb.dbo.LotEntity
>
> /* Example 2 */
> INSERT TableTwo (
> ColumnOne,
> ColumnTwo)
> SELECT
> ValueOne, -- ColumnOne
> ValueTwo
> FROM TableTwo
>|||The example code stands alone, using a table valued variable in place of my
real table, but the results are the same. When Example 1 is run it correctl
y
retuns the two rows are expected. When Example 2 is run it does not.
Now the freaky part I just discovered while doing these examples. I can
copy the problematic code block including the comment out of my main query
into a new query window and it fails. I then copy it into Notepad then back
out into a query window and it works.
Some of the code was created using a stored procedure that built the SQL
strings based on table structures and I used CHAR(13) for CRLF to format the
code. Is it possible that the absence of CHAR(10) is confusing SQL? The
code was edited and saved in SQL Server Management Studio after it was
generated and the piece of code that is failing was not generated; it was
created by hand, but was added the file that had generated code in it .
/* Example 1 */
SET NOCOUNT ON
DECLARE @.t TABLE(ID int)
INSERT @.t VALUES(1)
INSERT @.t VALUES(2)
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
INSERT tempdb.dbo.LotEntity (OldID)
SELECT ID
FROM @.t
SELECT * FROM tempdb.dbo.LotEntity
/* Example 1 results */
*** Create the LotEntity ID mapping table
OldID NewID
-- --
1 1
2 2
/* Example 2 */
SET NOCOUNT ON
DECLARE @.t TABLE(ID int)
INSERT @.t VALUES(1)
INSERT @.t VALUES(2)
PRINT '*** Create the LotEntity ID mapping table';
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'LotEntity')
DROP TABLE tempdb.dbo.LotEntity
CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
-- Comment
INSERT tempdb.dbo.LotEntity (OldID)
SELECT ID
FROM @.t
SELECT * FROM tempdb.dbo.LotEntity
/* Example 2 results */
*** Create the LotEntity ID mapping table
"Byron" wrote:

> It seem that using the old "--" style comment screws code in the IDE under
> certain circumstances. As an example, the code in example one should crea
te
> a temporary table, populate it with the current int ID from a table along
> with an int IDENTITY column that will serve as the new ID, then display th
e
> resulting temporary table. The code runs as expected without the -- Comme
nt
> line, but as soon as it is added the table no longer populates, and the
> temporary table is no longer displayed. The same type thing seems to happ
en
> when you place -- comments following values in a SELECT statement as in
> example two.
> I ran the code under both Query Analyzer and the new 2005 IDE and they bot
h
> exhibited thje same behavior, but I've used the -- comment style for years
> without an issue so I'm baffled. So far it only seems to affect SELECT
> statements.
> Has anyone else encountered this?
>
> /* Example One */
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT EntityID
> FROM dbo.tbl_LotEntities
> SELECT * FROM tempdb.dbo.LotEntity
>
> /* Example 2 */
> INSERT TableTwo (
> ColumnOne,
> ColumnTwo)
> SELECT
> ValueOne, -- ColumnOne
> ValueTwo
> FROM TableTwo
>|||> Some of the code was created using a stored procedure that built the SQL
> strings based on table structures and I used CHAR(13) for CRLF to format t
he
> code. Is it possible that the absence of CHAR(10) is confusing SQL?
Most probably. Make sure you have CHAR(13) + CHAR(10) and you should be fine
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:ACFB2214-1B3A-4794-B5ED-FE8B55D0B167@.microsoft.com...
> The example code stands alone, using a table valued variable in place of m
y
> real table, but the results are the same. When Example 1 is run it correc
tly
> retuns the two rows are expected. When Example 2 is run it does not.
> Now the freaky part I just discovered while doing these examples. I can
> copy the problematic code block including the comment out of my main query
> into a new query window and it fails. I then copy it into Notepad then ba
ck
> out into a query window and it works.
> Some of the code was created using a stored procedure that built the SQL
> strings based on table structures and I used CHAR(13) for CRLF to format t
he
> code. Is it possible that the absence of CHAR(10) is confusing SQL? The
> code was edited and saved in SQL Server Management Studio after it was
> generated and the piece of code that is failing was not generated; it was
> created by hand, but was added the file that had generated code in it .
> /* Example 1 */
> SET NOCOUNT ON
> DECLARE @.t TABLE(ID int)
> INSERT @.t VALUES(1)
> INSERT @.t VALUES(2)
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT ID
> FROM @.t
> SELECT * FROM tempdb.dbo.LotEntity
> /* Example 1 results */
> *** Create the LotEntity ID mapping table
> OldID NewID
> -- --
> 1 1
> 2 2
>
> /* Example 2 */
> SET NOCOUNT ON
> DECLARE @.t TABLE(ID int)
> INSERT @.t VALUES(1)
> INSERT @.t VALUES(2)
> PRINT '*** Create the LotEntity ID mapping table';
> IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
=
> 'LotEntity')
> DROP TABLE tempdb.dbo.LotEntity
> CREATE TABLE tempdb.dbo.LotEntity(OldID int, NewID int IDENTITY)
> -- Comment
> INSERT tempdb.dbo.LotEntity (OldID)
> SELECT ID
> FROM @.t
> SELECT * FROM tempdb.dbo.LotEntity
> /* Example 2 results */
> *** Create the LotEntity ID mapping table
>
> "Byron" wrote:
>|||Probably a good Idea to store the CHAR(13) + CHAR(10) in a variable so you
are not having to do that many calls for every line of dynamic code you
create. Yes its subsecond but if you throw millions of records at the
procedure you could run into some issues with performance.
"Tibor Karaszi" wrote:

> Most probably. Make sure you have CHAR(13) + CHAR(10) and you should be fi
ne.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Byron" <Byron@.discussions.microsoft.com> wrote in message
> news:ACFB2214-1B3A-4794-B5ED-FE8B55D0B167@.microsoft.com...
>