conditions, expressions

I have a table

CREATE TABLE [dbo].[CmnLanguage]
(
[Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY,
[EnglishName] [varchar](26) NOT NULL,
[NativeName] [nvarchar](26) NOT NULL,
[DirectionType] [smallint] NOT NULL,
[IsVisible] [bit] NOT NULL,
[CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[ModifiedDateTime] [datetime] NULL
)

We will use these 3 queries

select * from CmnLanguage where IsVisible = 0
select * from CmnLanguage where IsVisible = 1
select * from CmnLanguage

I want to make a method which handles these queries.

But at the back end on Stored Procedures

We have to write 3 queries

Which I don't want to do.

I want to minimize the queries and conditions

and want to just write one for these 3

Can any one do it?

How about this:

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOCREATE PROCEDURE dbo.sp_MyProcedure(@.IsVisibleAS BIT =NULL)ASBEGINSELECT*FROM[dbo].[CmnLanguage]WHERE[IsVisible] =CASEWHEN @.IsVisibleISNULLTHEN [IsVisible]ELSE @.IsVisibleENDENDGO

|||

Nice.

Very Useful.

Thanks.

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups
Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force Gro
upID Unique ONLY in case UserID is already there!
Is it possible?I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Grou
ps Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID U
nique ONLY in
case UserID is already there!
> Is it possible?|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>|||No, this enforces that you cannot have a duplicate of the *combination* of t
he columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
>
in[vbcol=seagreen]|||Actually your Right! Sorry for misunderstanding and thanks very much for the
quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of
the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for t
he quick response :-)
> "Tibor Karaszi" wrote:
>
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Conditional Constraint - HowTo

In the table below:
===========
Roles
===========
UserID (int)
GroupID
===========
Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
===========
Groups
===========
GroupID (int)
GroupName
===========
===========
Users
===========
UserID
Username
Password
Email
===========
And considering a User can have 0 or many Roles, exs:
Roles table
===========
1,1
1,2
1,3
===========
Meaning that this user have roles: 1,2,3
The problem is that I can have also:
Roles table
===========
1,1
1,1
1,1
===========
which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in case UserID is already there!
Is it possible?
I isn't this just a matter of letting both columns be the primary key?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> In the table below:
> ===========
> Roles
> ===========
> UserID (int)
> GroupID
> ===========
> Considering that UserID is FK from Users Table and GroupID is FK from Groups Table:
> ===========
> Groups
> ===========
> GroupID (int)
> GroupName
> ===========
>
> ===========
> Users
> ===========
> UserID
> Username
> Password
> Email
> ===========
> And considering a User can have 0 or many Roles, exs:
> Roles table
> ===========
> 1,1
> 1,2
> 1,3
> ===========
> Meaning that this user have roles: 1,2,3
>
> The problem is that I can have also:
> Roles table
> ===========
> 1,1
> 1,1
> 1,1
> ===========
> which is bad, so I need to have a constraint in the Roles table to force GroupID Unique ONLY in
case UserID is already there!
> Is it possible?
|||Hum, but then I believe there′ll be just 1 role per user?
"Tibor Karaszi" wrote:

> I isn't this just a matter of letting both columns be the primary key?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:131352F2-272C-4666-95A2-E153FB2C4932@.microsoft.com...
> case UserID is already there!
>
>
|||No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
fine:
1,2
1,3
2,2
2,3
Below is not fine:
1,2
1,2
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...[vbcol=seagreen]
> Hum, but then I believe therell be just 1 role per user?
> "Tibor Karaszi" wrote:
in[vbcol=seagreen]
|||Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
"Tibor Karaszi" wrote:

> No, this enforces that you cannot have a duplicate of the *combination* of the columns. So, below is
> fine:
> 1,2
> 1,3
> 2,2
> 2,3
>
> Below is not fine:
> 1,2
> 1,2
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KenA" <KenA@.discussions.microsoft.com> wrote in message
> news:7BF6E135-7CE1-4370-BF78-51032E0D8AFB@.microsoft.com...
> in
>
>
|||We all lose ourselves from time to time. Glad it helped. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KenA" <KenA@.discussions.microsoft.com> wrote in message
news:60CD2318-C000-4EC4-9C76-992096AD5F71@.microsoft.com...[vbcol=seagreen]
> Actually your Right! Sorry for misunderstanding and thanks very much for the quick response :-)
> "Tibor Karaszi" wrote:
below is[vbcol=seagreen]
ONLY[vbcol=seagreen]

Concatenation of integer data into text

I am a TSQL Newbie trying to concatenate two columns (DocumentNo & SequenceNo) that were created with a “smallint” data type constraint in a full-text search database.I want to end up with a column containing varchar data such as “5-2” where this row of data contains information about the 2nd document in a series for a person or group designated as 5.

If I could change the data type for the columns to varchar I think I could query them like this:

SELECT ("DocumentNo" + '-' + "SequenceNo") AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

When I try to concatenate with this query the result is a mathematical addition of the numbers, not what I am trying to achieve (which is to combine the two numbers to produce a text string).

Due to the full-text search parameters for the database I have not been able to modify the data type constraints on the two relevant columns.Is there a way to concatenate the two “smallint” columns and create a new column with text data (e.g., 5-2) for each row in the table?

My research suggests that “casting” could be used to convert between data types, but I have not been able to figure out how to apply it to my situation.Any help would be appreciated.

Casting should work.

It would be something like.

SELECT CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5)) AS DocumentNoFull

FROM Full_Documents

ORDER BY DocumentNo, SequenceNo

|||

Hi Ryan: Thanks, that was so easy. Now I know how to cast.

How do I create a new column in the database into which the results of the query will automatically be inserted?

|||

I'm not sure exactly what you mean.

Do you want to add a column to your table and populate it for all existing rows using your query? With this approach you would have to change future inserts to the table to populate this field. (Or use something like a trigger to populate it, if you don't have control of the insert statements)

Or do you want a computed column that is added to the table and then calculated based on the values in the other fields?

Can I ask why you need to add this as a column at all? Why can't you just do the concatenation in SQL when you need it?

If you really need to do either the first option or second, I can point you toward how to do it.

|||

I think I want the first option. I don't foresee any additions to the database (which is based on historical records from a closed source).

I hope to be able to do full text searches in a VB application and possibly from a web form and am looking to keep things simple when I write those applications. As I get more experience I will surely become more confident in my ability to concatenate, etc. But at this point I just want to make sure I can get it to work. I can do full text searches easily from within SQL Management Studio, but have not yet been able to achieve it from Visual Basic. So I just want to eliminate as many possible sources of error until I know that I can do it all properly.

Also, I will learn to create a new column and insert data from a query (which could be useful as I progress in my TSQL education).

|||

Okay. If you really want the first option.

Do something like this. For the added column you either need to allow it to be NULL or give it a default value. I went with the NULL option

ALTER TABLE Full_Documents ADD concat_col VARCHAR(15) NULL

UPDATE Full_Documents SET concat_col = CAST(DocumentNo AS VARCHAR(5) )+ '-' + CAST(SequenceNo AS VARCHAR(5))

|||

Thanks Ryan. Exactly what I wanted in this instance.

Just so that I will understand my choice - would the second option have created a dynamic field that would have automatically been updated with the properly concatenated text when a new row was added? If not, what did I miss by choosing the first option?

|||

Yes, that is exactly the difference. You can use what is called a computed column. From a performance standpoint, it is not usually the best idea. But, you can declare that column using a function that returns the value that you want. With this column, the concat_col would always have values associated with the other 2 columns instead of needing it to be inserted with each row.

The typical way to do this is to declare the column with a type that references a function (instead of varchar). The function would return the value that you want based on the other values in your row.

|||Thanks again Ryan.