joining text in SP in SQL SERVER

here is my code:

declare @.strSQL nvarchar(4000);

set @.strSQL = 'select emailAddress from tblMailingList where mailingID > 0 ' ;
--send Mail
if len(@.sendMail)>0
@.strSQL+=' and sendMail =@.sendMail'

but I got this error:

Incorrect syntax near '@.strSQL'.

how can I joining the @.strSQL if the "if statement" is true?

thanks!

24sharon:

@.strSQL+=' and sendMail =@.sendMail'

Try this instead:

set @.strSQL = @.strSQL + ' and sendMail = ''' + @.sendMail + ''''

Hope this will help.

|||

at last line u have some wrong code. replace last line using -->

Set @.strSQL = @.strSQL + ' and sendMail = ''' + @.sendMail + ''''

Joining tables based on string key - bad idea?

The data already in the tables allows me to obtain what I need by drawing a relationship based on two columns that have nvarchar values but I have noticed that generally tables are related through integer keys. I want to know whether there are downsides to doing this, specifically if the join condition could 'wrongly' fail due to the string nature of the join criteria and thus cause missing rows in my resulting table.

Its typically not that great of an idea because it is going to take SQL SErver longer to join on these fields because they are larger than integer fields. The larger the field value, the longer it takes to compare (and in this case, join).
Tim|||

As Tim indicated, string values for JOINs is generally not a good idea. It has to do with how many bytes of data that has to be stored and read from the indexes. The shorter the values, the quicker index searching becomes.

However, if the string values are 'short' ( < 10 characters ), and the columns are indexed, it will most likely perform fine for you. The variables include the total number of rows in the table, amount of table activity, etc. I would NOT allow these string keys to be easily (if ever) changed.

|||

If you use character columns...

it's also a good idea to put adequate constraints on the columns or define foreign references to help ensure the quality of the data, so you don't end up with broken relationships

Join problem

Hi

CREATE TABLE [dbo].[Users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Users] VALUES('Unal')
INSERT INTO [dbo].[Users] VALUES('Volkan')
INSERT INTO [dbo].[Users] VALUES('Duygu')
INSERT INTO [dbo].[Users] VALUES('Elif')
INSERT INTO [dbo].[Users] VALUES('Mehmet')
INSERT INTO [dbo].[Users] VALUES('Demir')

CREATE TABLE [dbo].[Agenda](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ActivityName] [nvarchar](50) NULL,
[UserId] [nvarchar](50) NULL
) ON [PRIMARY]

INSERT INTO [dbo].[Agenda] VALUES('school excursion','1,3,4')
INSERT INTO [dbo].[Agenda] VALUES('party','6,2,3,1')

Table Name : Users

Id UserName

1 Unal

2 Volkan

3 Duygu

4 Elif

5 Mehmet

6 Demir

---

Table Name : Agenda

Id ActivityName UserId

1 school excursion 1,3,4

2 party 6,2,3,1

---

I want to join Users table with Agenda table.

The Result has to be like below:

Id ActivityName UserId UserNames

1 school excursion 1,3,4 Unal, Duygu, Elif

2 party 6,2,3,1 Demir, Volkan, Duygu, Unal

How can I do it in SQL 2005. I don't want use cursor.

OR

if I can't do that join, I have to do other join that it's below.

Id ActivityName UserId UserNames

1 school excursion 1 Unal

1 school excursion 3 Duygu

1 school excursion 4 Elif

2 party 6 Demir

2 party 2 Volkan

2 party 3 Duygu

2 party 1 Unal

thanks so much for help

What you are attempting to accomplish is a form of denormalization. While it is not a 'straightforward' task in T-SQL, here are links to a couple of approaches that may work for you.

Lists -Field Concatenation, One Field to Itself for string
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx

|||

Thanks everbody who help and answer.

join on column with different data types

Hi.
I have a query where a single column is used as join key.
In TableA the column is nvarchar and in TableB it is varchar.
TableB has a clustered index on that column but the join will do a full
table scan
on TableB.
When I convert the coresponding column in TableA to varchar the query use
the index.
The join query will not do an implicit convert between varchar and
nvarchar.
Perhaps as designed.
Where can I find documentation on this issue.
I have been searching books online and Googles but haven't found it.
Sqlserver 2000 or 7.0
--
/dg
----
Dan van Ginhoven
SchlumbergerSema AB
S-412 97 GÖTEBORG Sweden
Phone +46 317 51 44 13
Mob/Cell +46 708 51 44 13convert(varchar(10, column_tableA) = column_tableB
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||There is a chart which shows which data types are implicitly or explicitly
convertable in books on line search for convert... The chart indicates that
SQL can implicitly convert between nchar/nvarchar and char/varchar
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||Hi Wayne!
Yes I have seen the chart. It surprises me a bit that the query didn't use
the index.
It may be bug.
I´m looking for a text that describes in what situations the Query Planner
will not use an
index, but will do a full table scan. I think I have seen it once.
One example is when a query contains <column> like '%value%'
it won´t use an index on that column to solve the query.
/dg|||> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
Nope, it is how SQL Server works. Although you don't have to write any code
for an implicit conversion SQL Server still converts one datatype to another
when it creates the execution plan. If you run the following code in Query
Analyzer and look at the execution plan you will see that #B.B is converted
before the two tables are joined:
CREATE TABLE #A (A nvarchar(20))
GO
CREATE TABLE #B (B varchar(20))
GO
SELECT * FROM #A
INNER JOIN #B
ON #A.A = #B.B
GO
DROP TABLE #A, #B
GO
Why is #B.B converted and not #A.A? That is determined by the Data Type
Precedence. varchar has a lower Data Type Precedence than nvarchar, so
varchar gets converted. (You can find the complete list of the data type
precedence in Books Online under Data Type Precedence).
Because #B.B is used in a function (when it is converted), the Query
Optimizer can't use any indexes on the column and has to use a table scan.
As you saw when the other (nvarchar) column in the join is explicitly
converted, the varchar column won't be implicitly converted and the index on
the varchar column can be used.
About a text: Kalen Delaney has written a series of articles for SQL Server
Magazine (www.sqlmag.com) about which search conditions can make use of
indexes and which don't, and there is also a bit about it in her book Inside
SQL Server 2000.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uPHxWevPDHA.1720@.TK2MSFTNGP11.phx.gbl...
> Hi Wayne!
> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
> I´m looking for a text that describes in what situations the Query
Planner
> will not use an
> index, but will do a full table scan. I think I have seen it once.
> One example is when a query contains <column> like '%value%'
> it won´t use an index on that column to solve the query.
> /dg
>