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 default value

Hi,
How can i change default value in sql server through SQL script if i haven't
given default constraint name and using system default generated constraintg
name ?
I have 50 tables to change the default value of a particular field...pls
advice what to do...i don't want to open every table in design mode and
change the default value...pls suggest some script to do it.
With regards,
Gurmeet SinghThis scenario is exactly why you should name your constraint in the first
place...
If you want to automate this, you can use the system tables to get the
constraint name and use that name in your ALTER TABLE ... DROP CONSTRAINT
... statements. Note that SQL Server doesn't accept a variable for table or
constraint name, so you have do use dynamic SQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||Hi,
Refer books online for the below commands,
Alter table drop constraint <Const_name>
Alter table add constraint
Thanks
Hari
MCDBA
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>|||You can remove the default from a column using the following script:
-- remove the default
DECLARE @.constraint_name SYSNAME
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.name = '<table name>'
AND c_obj.xtype = 'D'
AND cols.[name] = '<column name>')
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
You can adjust this to use multiple tables and to add the new constraint as
well.
Jacco Schalkwijk
SQL Server MVP
"Gurmeet" <gurmeetsm@.hotmail.com> wrote in message
news:uBmwsJ1BEHA.624@.TK2MSFTNGP10.phx.gbl...
> Hi,
> How can i change default value in sql server through SQL script if i
haven't
> given default constraint name and using system default generated
constraintg
> name ?
> I have 50 tables to change the default value of a particular field...pls
> advice what to do...i don't want to open every table in design mode and
> change the default value...pls suggest some script to do it.
> With regards,
> Gurmeet Singh
>

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
--