Change local variable inside query

/*Given*/

CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)

/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.

This doesn't compile of course, but is there
a way to accomplish the same thing?
*/

DECLARE @.ndx int

SET @.ndx = 1

SELECT

(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT

FROM _T1sub a

/*Output would look like this:*/

FKplusWT
----
11
22
33

/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/

thanks, Otto PorterOn Sat, 02 Oct 2004 12:20:48 -0600, Otto Porter wrote:

>I know, I can get this output just by adding
>FK+WT. This is not about that.
>This is about setting vars inside a query

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Change condition if the first doesn't exists

Have two tables:

code:

CREATE TABLE [Table1]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Number] [varchar] (50) NOT NULL ,
[TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
DEFAULT (getdate()),
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [Table2]
(
[Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Table1Id] [int] NOT NULL ,
[LingoId] [int] NOT NULL ,
[Header] [nvarchar] (150) NOT NULL ,
[Description] [ntext] NOT NULL ,
CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
(
[Table1Id]
) REFERENCES [Table1] (
[Id]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

What I would like is that if LingoId = 2 in the query below doesn't exists
than it should fall thru and use the values where LingoId = 1. LingoId = 1
always exists for each Table2.Id. Is this doable?

code:

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = 2) --Something should happen here I quess.

SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Header ELSE
T2_1.Header END) as Header,
(CASE WHEN T2_2.Existing IS NOT NULL THEN T2_2.Description ELSE
T2_1.Description END) as Description
FROM
dbo.Table1
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 2) T2_2
ON dbo.Table1.Id = T2_2.Table1Id
LEFT OUTER JOIN
(SELECT dbo.Table2.Header, dbo.Table2.Description, 'Exists'
Existing FROM Table2 Where Lingold = 1) T2_1
ON dbo.Table1.Id = T2_1.Table1Id
HTH, Jens Suessmeyer.|||Got an answer elsewhere that did the trick.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||First, you are filtering the result with values from table2. That will
effectively be an INNER JOIN. Did you mean it like that?
If you want to use only one query then something like this could be good
enough:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
LEFT OUTER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
(dbo.Table2.LingoId = case when exists (select table1.id from table1
inner join table2 on table1.id = table2.id where table2.LingoID = 2) then 2
else 1 end)
MC
"Senna" <
Senna@.discussions.microsoft.com>
wrote in message
news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||The solution, as I mention above, looked liked this:
SELECT
dbo.Table1.Id,
dbo.Table1.Number,
dbo.Table1.[TimeStamp],
dbo.Table2.Header,
dbo.Table2.Description
FROM
dbo.Table1
INNER JOIN
dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
WHERE
dbo.Table2.LingoId = 2
or (dbo.Table2.LingoId = 1
and not exists
(select *
from table2 cn
where cn.table1id = dbo.Table2.Table1Id
and cn.lingoid = 2))
ps. Thank for your time and answer Jens.
"Senna" wrote:

>
Have two tables:
>
>

code:

>
 CREATE TABLE [Table1]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Number] [varchar] (50) NOT NULL ,
>
 [TimeStamp] [smalldatetime] NOT NULL CONSTRAINT [DF_Table1_TimeStamp]
>
 DEFAULT (getdate()),
>
 CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY]
>
 ) ON [PRIMARY]
>
 GO
>
>
 CREATE TABLE [Table2]
>
 (
>
 [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>
 [Table1Id] [int] NOT NULL ,
>
 [LingoId] [int] NOT NULL ,
>
 [Header] [nvarchar] (150) NOT NULL ,
>
 [Description] [ntext] NOT NULL ,
>
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED
>
 (
>
 [Id]
>
 ) ON [PRIMARY] ,
>
 CONSTRAINT [FK_Table2_Table1] FOREIGN KEY
>
 (
>
 [Table1Id]
>
 ) REFERENCES [Table1] (
>
 [Id]
>
 )
>
 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>
 GO
>

>
>
What I would like is that if LingoId = 2 in the query below doesn't exists
>
than it should fall thru and use the values where LingoId = 1. LingoId = 1
>
always exists for each Table2.Id. Is this doable?
>
>

code:

>
 SELECT
>
 dbo.Table1.Id,
>
 dbo.Table1.Number,
>
 dbo.Table1.[TimeStamp],
>
 dbo.Table2.Header,
>
 dbo.Table2.Description
>
 FROM
>
 dbo.Table1
>
 LEFT OUTER JOIN
>
 dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
>
 WHERE
>
 (dbo.Table2.LingoId = 2) --Something should happen here I quess.
>

|||Yes, it was an inner join. :)
See the other post with the solution I went with. Thanks anyway for your
time and effort.
"MC" wrote:

> First, you are filtering the result with values from table2. That will
> effectively be an INNER JOIN. Did you mean it like that?
> If you want to use only one query then something like this could be good
> enough:
> SELECT
> dbo.Table1.Id,
> dbo.Table1.Number,
> dbo.Table1.[TimeStamp],
> dbo.Table2.Header,
> dbo.Table2.Description
> FROM
> dbo.Table1
> LEFT OUTER JOIN
> dbo.Table2 ON dbo.Table1.Id = dbo.Table2.Table1Id
> WHERE
> (dbo.Table2.LingoId = case when exists (select table1.id from table1
> inner join table2 on table1.id = table2.id where table2.LingoID = 2) then
2
> else 1 end)
>
> MC
>
> "Senna" <Senna@.discussions.microsoft.com> wrote in message
> news:7335ADA4-EF90-4525-B513-ACD9F64CB353@.microsoft.com...
>
>

CDC not tracking changes

Hi

I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.

any ideas ?

thanks

CDC is a SQL Server 2008 feature. Not to mention nothing to do with SSIS. Can you refine your issue so that we can redirect your question appropriately? This is a SQL Server 2005 forum for the SQL Server Integration Services tool.

|||

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

|||

Sqlgoof wrote:

sorry Phil, but when i search on the forums under "change data capture" - only SSIS forums come up. I'll ask the question internally - i'm in Microsoft. thanks for your troubles.

Try the SQL Server 2008 Data Warehousing forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1617&SiteID=1

cast inside check constraint??

I get an error when I try to do cast inside check constraint like the
following:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
)
Please help.why can't you just use a unique constraint on (mytblid, month, year)?
Anyway, you can use a computed column
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1, 1) NOT NULL ,
[month] [int] NOT NULL ,
[year] [int] NULL,
[mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
varchar(4))
)
GO
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid], [mon_slash_yr]
)
go
drop table [dbo].[mytbl]
go|||This is not a check constraint. You specified a unique constraint. Casts
are allowed in check constraints. Unique constraints can only contain
column names.
Please specify what you want to achieve, because unless you are using
IDENTITY_INSERT ON, all rows in this table will be unique regardless of
the month/year setting.
Gert-Jan
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||Thanks
"Alexander Kuznetsov" wrote:

> why can't you just use a unique constraint on (mytblid, month, year)?
> Anyway, you can use a computed column
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL,
> [mon_slash_yr] as cast(month as varchar(2)) + '/'+ cast(year as
> varchar(4))
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid], [mon_slash_yr]
> )
> go
> drop table [dbo].[mytbl]
> go
>|||that's a unique constraint, not a check constraint
just make it on all three columns
ALTER TABLE [dbo].[mytbl] ADD
CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
(
[mytblid],
[month],
[year]
)
sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.|||sqlster wrote:
> I get an error when I try to do cast inside check constraint like the
> following:
> CREATE TABLE [dbo].[mytbl] (
> [mytblid] [int] IDENTITY (1, 1) NOT NULL ,
> [month] [int] NOT NULL ,
> [year] [int] NULL
> )
> GO
>
> ALTER TABLE [dbo].[mytbl] ADD
> CONSTRAINT [myconstraint] UNIQUE NONCLUSTERED
> (
> [mytblid],
> cast(month as varchar(2)) + '/'+ cast(year as varchar(4))
> )
> Please help.
I guess this is what you need:
CREATE TABLE [dbo].[mytbl] (
[mytblid] [int] IDENTITY (1,1) NOT NULL
CONSTRAINT pk_mytbl PRIMARY KEY ,
[month] [int] NOT NULL ,
[year] [int] NOT NULL
)
GO
ALTER TABLE dbo.mytbl
ADD CONSTRAINT myconstraint
UNIQUE NONCLUSTERED ([month], [year])
GO
David Portas
SQL Server MVP
--

Cast a null to decimal?

I have to do a join to a table that may have expenses incurred for a
commission report.
How do I cast that null to a 0.00 so I can subtract the expense from the
SalesPrice in a single pass query?
TIA
__StephenHi,
Use ISNULL function:-
ISNULL(fieldname,0.00)
Thanks
Hari
SQL Server MVP
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:eieXRMyWFHA.2288@.TK2MSFTNGP14.phx.gbl...
>I have to do a join to a table that may have expenses incurred for a
> commission report.
> How do I cast that null to a 0.00 so I can subtract the expense from the
> SalesPrice in a single pass query?
> TIA
> __Stephen
>|||Use Coalesce like so:
Coalesce(ColumName, 0.00)
Thomas
"Stephen Russell" <srussell@.lotmate.com> wrote in message
news:eieXRMyWFHA.2288@.TK2MSFTNGP14.phx.gbl...
>I have to do a join to a table that may have expenses incurred for a
> commission report.
> How do I cast that null to a 0.00 so I can subtract the expense from the
> SalesPrice in a single pass query?
> TIA
> __Stephen
>