doubt

hi,

How to search a word in the sqltable?.ie suppose the field name has data type nvarchar(200). From there i enter the persional address of that persion. now i want to know the information whoes are coming from particular city?. The city value can enter in the address field. how we can make search?...

with regards,
ks.kumar

*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...753175428d29c27
*****************************************You can use LIKE:

... WHERE col LIKE '%London%'

but this type of search is costly.

If you need to do this type of search then I suggest you separate the
address with a different column for each element: building, street, city,
state/region, postal code, country, etc. This is much easier and more
efficient to search.

--
David Portas
SQL Server MVP
--

Double Table Insert

Tables :

EmailUsers
ID int - PK
Email nvarchar(256)

ListsUsers
ListID int - FK to List Table - Combo PK
UserID int - FK to EmailUsers Table - Combo PK

When a person adds a user I need to:
A. insert them as a new entry into EmailUsers - no problem
B. insert their EmailUsers.ID from step A and ListID (passed in parameter) into ListsUsers - not so easy
C. if they're already in EmailUsers don't insert them but pass their existing EmailUsers.ID to part B

Any thoughts or examples I can follow? Maybe it's easier to do two seperate queries and control the if exists logic in asp.net?I would check out the starter kits--such as the TimeTracker. They illustrate the following:

if select Count(*) from emailusers where .. = 0
Begin
Begin Transaction

insert into Email users

select @.id = @.@.identity

if no error
continue

insert into ListUsers
(x, @.id)

Commit Trans
end
else
select @.id = id from emailusers

insert into ListUsers
(x, @.id)

Hope this points you in the right direction|||

CREATE PROCEDURE SaveUser
 @.EmailAddress VARCHAR(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 SELECT @.UserID = ID FROM EmailUsers WHERE Email = @.EmailAddress
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO EmailUsers (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 END
 INSERT INTO ListUsers (ListID, UserID) VALUES (@.ListID, @.UserID)

|||I'd say use SCOPE_IDENTITY() rather than @.@.IDENTITY.. if there was another insert at the same time @.@.IDENTITY will return the id of that insert. SCOPE_IDENTITY() will work within the scope of the insert..

hth|||ndinakar: Thanks for that little tidbit, I didn't realize there was a potential for scope issues with @.@.IDENTITY.|||it should be ok to use @.@.IDENTITY as there could be 1/1000 ( or more or less...just a guesstimate) chance of your insert coinciding with another one but that could very well runi your day...so...check out BOL for more info on them...

hth|||I was in Vegas for the past week. Will give the suggestions here a try on Monday morning.

Thanks!|||Works great but I'm slightly confused on my return value

CREATE PROCEDURE [dbo].[Add_List_Users]
 @.EmailAddress nvarchar(256),
 @.ListID INT
AS
 DECLARE @.UserID INT
 DECLARE @.returnCode INT
 SELECT @.UserID = ID FROM Email_Users WHERE Email = @.EmailAddress
 SET @.returnCode = 0
 IF @.UserID IS NULL
 BEGIN
 INSERT INTO Email_Users (Email) VALUES (@.EmailAddress)
 SET @.UserID = @.@.IDENTITY
 SET @.returnCode = @.returnCode + 1
 END
if not exists (select * from Lists_Users where ListID = @.ListID and UserID = @.UserID)
 INSERT INTO Lists_Users (ListID, UserID) VALUES (@.ListID, @.UserID)
 SET @.returnCode = @.returnCode + 2
return @.returnCode
GO

Scenarios:
User Added to Users Table and List Table - returnCode 3
User exists in Users Table and Added to List Table - return code 2
User exists in both tables - return code 0

Actual Return Values:
2
1
-1

That's fine, I just check for 2, 1, -1 instead of 3, 2, 0 but I'd like to know why the values returned aren't logically what they should be?

SET @.returnCode = 0
SET @.returnCode = @.returnCode + 1
SET @.returnCode = @.returnCode + 2
shouldn't that = 3??

It's as if @.returnCode starts off at -1 since all the return values are 1 less than I expect them to be.|||The SCOPE_IDENTITY really comes into play if you've got hidden inserts. For example, another insert triggered from your original insert. That's where simply returning @.@.IDENTIY becomes totally wrong. Like the poster said, use SCOPE_IDENTITY and you can sleep at night!|||plus..you might want to look at OUTPUT Parameters to return the id ..check out BOL for more info on OUTPUT parameters..

hth

Don't Know How To Explain It

The tables are as follows
CREATE TABLE dbo.Blogs
dId int * PRIMARY
posted datetime(8)
title nvarchar(50)
catgy nvarchar(50)
blogs nvarchar(500)
picId int
UName nvarchar(50)
CREATE TABLE dbo.BlogPix
bgId int * PRIMARY
picId int
Photo nvarchar(50)
Caption(50)
UName nvarchar(50)
I dont know how to explain it but this is the way i have the application
working. The UName on every table in the system is the User name of the User
and it returns rows filtered by UName = @.Uname. However this works fin on my
application but it gets tricky when i try to filter by picId. Ok, the picId
is not a autonumber and any user can get the same number at some time
because i am using the random class to generate a number from the smallest
INT32 to the biggest so i have big scope.
its so hard to explain what i mean so i will give an example,
SELECT TOP (30) *
FROM Blogs INNER JOIN
BlogPix ON Blogs.picId = BlogPix.picId
WHERE UName = @.UName AND PicId = @.PicId
basically i want to retrieve data by using the keyfields dId or picId which
ever way i can so i can return results like the following;
bgId !!!!!!! picId !!!!!!! Photo !!!!!!! Caption !!!!!!! UName
--1--99995688--~/pix/plig.jpg--im a minger--Sean--
--2--99995688--~/pix/pliglet.jpg--im a minger--Sean--
--3--99995688--~/pix/pligkil.jpg--im a minger--Sean--
--4--99995688--~/pix/plipim.jpg--im a minger--Sean--
--5--99995688--~/pix/plim.jpg--im a minger--Sean--
dId !!!!!!! picId !!!!!!! Title !!!!!!!!!!!!!!!!!! Blogs
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Posted
!!!!!!!!!!! Catgy !!!!!!! UName
--1--99995688--Me On A Gud Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
--2--2568777--Me On A Bad Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
--3--12369875--Me On A Nasty Day--Im going gud and life is
great--21 Aug 2005--Music--Sean--
--4--56888999--Me On A Lovely Day--Im going gud and life is
great--21 Aug 2005--Music--Sean--
--5--5896633--Me On A Dark Day--Im going gud and life is great--21
Aug 2005--Music--Sean--
Hope this helps, i cant really explain what it is but i do knowI think you'll find your answers in the following article:
http://www.sommarskog.se/dyn-search.html
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Eamon Straughn" <blackmanvandam@.aol.com> wrote in message
news:uUu9KJCrFHA.2076@.TK2MSFTNGP14.phx.gbl...
> The tables are as follows
> CREATE TABLE dbo.Blogs
> dId int * PRIMARY
> posted datetime(8)
> title nvarchar(50)
> catgy nvarchar(50)
> blogs nvarchar(500)
> picId int
> UName nvarchar(50)
> CREATE TABLE dbo.BlogPix
> bgId int * PRIMARY
> picId int
> Photo nvarchar(50)
> Caption(50)
> UName nvarchar(50)
> I dont know how to explain it but this is the way i have the application
> working. The UName on every table in the system is the User name of the
User
> and it returns rows filtered by UName = @.Uname. However this works fin on
my
> application but it gets tricky when i try to filter by picId. Ok, the
picId
> is not a autonumber and any user can get the same number at some time
> because i am using the random class to generate a number from the smallest
> INT32 to the biggest so i have big scope.
> its so hard to explain what i mean so i will give an example,
> SELECT TOP (30) *
> FROM Blogs INNER JOIN
> BlogPix ON Blogs.picId = BlogPix.picId
> WHERE UName = @.UName AND PicId = @.PicId
> basically i want to retrieve data by using the keyfields dId or picId
which
> ever way i can so i can return results like the following;
> bgId !!!!!!! picId !!!!!!! Photo !!!!!!! Caption !!!!!!! UName
> --1--99995688--~/pix/plig.jpg--im a minger--Sean--
> --2--99995688--~/pix/pliglet.jpg--im a minger--Sean--
> --3--99995688--~/pix/pligkil.jpg--im a minger--Sean--
> --4--99995688--~/pix/plipim.jpg--im a minger--Sean--
> --5--99995688--~/pix/plim.jpg--im a minger--Sean--
> dId !!!!!!! picId !!!!!!! Title !!!!!!!!!!!!!!!!!! Blogs
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!Posted
!!!!!!!!!!! Catgy !!!!!!! UName
> --1--99995688--Me On A Gud Day--Im going gud and life is
great--21
> Aug 2005--Music--Sean--
> --2--2568777--Me On A Bad Day--Im going gud and life is great--21
> Aug 2005--Music--Sean--
> --3--12369875--Me On A Nasty Day--Im going gud and life is
> great--21 Aug 2005--Music--Sean--
> --4--56888999--Me On A Lovely Day--Im going gud and life is
> great--21 Aug 2005--Music--Sean--
> --5--5896633--Me On A Dark Day--Im going gud and life is
great--21
> Aug 2005--Music--Sean--
> Hope this helps, i cant really explain what it is but i do know
>