contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have any idea?
I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?
|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?
|||thank you it works

contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them in my stored procedure like
select Productname+CustomerID az newI
but I have error becouse one of them are int another in char ddo you have any idea?I suggest that you read about "Datatype Precedence" in Books Online. It will explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (CustomerID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works :)

contact 2 column

Hi I have 2 coloumn one hast char(Productname) type another has int type (Cu
stomerID)I want to contact them in my stored procedure like:
select Productname+CustomerID az newID
but I have error becouse one of them are int another in char ddo you have an
y idea?I suggest that you read about "Datatype Precedence" in Books Online. It will
explain what that fail. SQL
Server will try to convert the string to an int. You have to use an explicit
CAST:
SELECR Productname + CAST(CustomerID AS varchar(20))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type (Customer
ID)I want to contact them
in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have any idea
?|||mahsa
CREATE TABLE #Test
(
col INT,
col1 VARCHAR(10)
)
GO
INSERT INTO #Test VALUES (1,'AA')
INSERT INTO #Test VALUES (2,'BB')
GO
SELECT CAST(col AS VARCHAR(10))+col1 FROM #Test
"mahsa" <anonymous@.discussions.microsoft.com> wrote in message
news:F40E28D3-9983-4229-9BDC-ECCF73F4E229@.microsoft.com...
> Hi I have 2 coloumn one hast char(Productname) type another has int type
(CustomerID)I want to contact them in my stored procedure like:
> select Productname+CustomerID az newID
> but I have error becouse one of them are int another in char ddo you have
any idea?|||thank you it works

Constraint

Hi,
we want to check the paystatusid in the table Treatement
CREATE TABLE [Treatement] (
[treatementid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[paystatusid] int NOT NULL , ....
and
CREATE TABLE [Paystatus] (
[paystatusid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,...
so that the Paystatus it refers to has the same customerid.
It is also accepted that paystatusid of the Treatement table is 0 and hence
dont refer to a Paystatus.
Olav"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@.microsoft.com...
> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
> [treatementid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,
> [paystatusid] int NOT NULL , ....
> and
> CREATE TABLE [Paystatus] (
> [paystatusid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,...
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and
> hence
> dont refer to a Paystatus.
> Olav
>
You have a few choices here (listed in my order of preference)
1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
table.
2. Use a trigger to handle your special case of a 0 paystatusid.
3. Use a stored procedure to perform these validations.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick,
i am temped by the FK alternative:
But there is an foreign key on the customerid referencing the customer
table. So in the Treatement table customerid will alway have a value but
paystatusid can be 0 meaning it is not yet connected to an Paystatus object.
Still possible? If still so please describe it.
Regards,
Olav
"Rick Sawtell" wrote:

> "Olav" <Olav@.discussions.microsoft.com> wrote in message
> news:B72A4068-27AB-4C99-83CC-982ABB0DC79A@.microsoft.com...
> You have a few choices here (listed in my order of preference)
> 1. Set up your FK constraint and include a 0 paystatusid in the PayStatus
> table.
> 2. Use a trigger to handle your special case of a 0 paystatusid.
> 3. Use a stored procedure to perform these validations.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Olav wrote:
> Hi,
> we want to check the paystatusid in the table Treatement
> CREATE TABLE [Treatement] (
> [treatementid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,
> [paystatusid] int NOT NULL , ....
> and
> CREATE TABLE [Paystatus] (
> [paystatusid] int IDENTITY(1,1) NOT NULL ,
> [customerid] int NOT NULL ,...
> so that the Paystatus it refers to has the same customerid.
> It is also accepted that paystatusid of the Treatement table is 0 and henc
e
> dont refer to a Paystatus.
> Olav
There's very little information to go on here. As a minimum, please
include primary/unique key declarations with your DDL.
Why do paystatusid and customerid both appear in both tables? If you do
that you surely ought to have a foreign key in place.
David Portas
SQL Server MVP
--|||Okay here it comes, i will drop the nonerelevalnt columns:
CREATE TABLE [Treatement] (
[treatementid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[appbookid] int NOT NULL ,
[paystatusid] int NOT NULL ,
CONSTRAINT [PK_Treatement_treatementid] PRIMARY KEY NONCLUSTERED
([treatementid]),
CONSTRAINT [FK_Treatement_Appbook] FOREIGN KEY ([appbookid]) REFERENCES
[dbo].[Appbook] ([appbookid]),
CONSTRAINT [FK_Treatement_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))
CREATE TABLE [Paystatus] (
[paystatusid] int IDENTITY(1,1) NOT NULL ,
[customerid] int NOT NULL ,
[appbookid] int NOT NULL ,
CONSTRAINT [PK_Paystatus] PRIMARY KEY CLUSTERED ([paystatusid]),
CONSTRAINT [FK_Paystatus_Customer] FOREIGN KEY ([customerid]) REFERENCES
[dbo].[Customer] ([customerid]))
Olav
"David Portas" wrote:

> Olav wrote:
> There's very little information to go on here. As a minimum, please
> include primary/unique key declarations with your DDL.
> Why do paystatusid and customerid both appear in both tables? If you do
> that you surely ought to have a foreign key in place.
> --
> David Portas
> SQL Server MVP
> --
>