Adding A default value in a column in a declare statment

Hi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

ThanksHi Can anybody help me.

I have this querey at the minute but when I attempt to put the default value of IND in the Sales Rep Code field I get an error Invalid column name, if I try and put 'IND' AS [Sales Rep Code] I get an incorrect syntax error.

DECLARE @.Query nVarchar(1000)
SET @.Query = N'SELECT NULL AS [GEO UNIT], NULL AS [PMC Invoice Date], IND AS [Sales Rep Code] FROM '+ 'Test' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10') THEN 'Q4' END END END END + '.dbo.all_data' EXECUTE sp_executesql @.Query, N'@.level tinyint', @.level = 35

Can anybody please help on this.

Thanks

would help us if u could post the ddl for the table|||what is the dataytpe of [Sales Rep Code]?
Off the cuff, it looks like your IND value is supposed to be a string.
Your example would be OK if you use 'IND' as [Sales Rep Code] - with the single quotes around IND.|||hi,

The datatype for SalesRep code is not defined as its in a view and I am creating the column [Sales Rep Code] with the default value of IND,

e.g

Select 'IND' AS [Sales Rep Code] from test would create a [Sales Rep Code] populated by IND for each row.

I have tried the 'IND' and it appears that it is incorrect syntax, this is beacuse it is closing the string value of the declare statment at the fist ' but I dont have a way around this.

Any Ideas|||Hi thanks for everyones help finally got it working but I still have one problem in that it is over 4000 carachters long and the @.test has a maximum of 4000 carachters is there any way around this.

Thanks

Actual size of Getdate()

what is the actual length of the result returned by getdate()?
When you set the mode Results in text in QA and run this ,
declare @.s1 varchar(20)
declare @.s2 varchar(30)
set @.s1='no'
set @.s2='the'
select @.s1,@.s2
select getdate()
you get the following result
-- --
no the
(1 row(s) affected)
---
2006-03-16 14:51:26.140
(1 row(s) affected)
The number of '-' is the length of the column or varialbe which
correctly matches with the size of @.s1 and @.s2
But for getdate(), why does it display 54 '-'s? Does it mean the
maximum length is 54?
MadhivananGETDATE returns datetime, not a string. It is the client that does the forma
tting from datetime
(binary information) to something which is readable for us humans. The clien
t application can adapt
to regional settings on the client machine. SQL Server has no control of how
this is presented at
the client. I imagine that some locales can end up with pretty long strings
to represent a datetime,
hence the rather long meta-data definition of such a column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1142950451.878083.249060@.u72g2000cwu.googlegroups.com...
> what is the actual length of the result returned by getdate()?
> When you set the mode Results in text in QA and run this ,
> declare @.s1 varchar(20)
> declare @.s2 varchar(30)
> set @.s1='no'
> set @.s2='the'
> select @.s1,@.s2
> select getdate()
> you get the following result
>
> -- --
> no the
> (1 row(s) affected)
>
> ---
> 2006-03-16 14:51:26.140
> (1 row(s) affected)
> The number of '-' is the length of the column or varialbe which
> correctly matches with the size of @.s1 and @.s2
> But for getdate(), why does it display 54 '-'s? Does it mean the
> maximum length is 54?
> Madhivanan
>|||That is interesting
run these 2
select convert(varchar,getdate(),109)
select getdate()
first one is longer but has less -
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> select convert(varchar,getdate(),109)
You didn't specify a length for the varchar. In all but two cases, that will
result in varchar(1).
The two exceptions are inside CAST and CONVERT where you get varchar(30). Th
is is why you see a
length of 30 for this column.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1142951590.221838.143270@.g10g2000cwb.googlegroups.com...
> That is interesting
> run these 2
> select convert(varchar,getdate(),109)
> select getdate()
> first one is longer but has less -
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||> You didn't specify a length for the varchar. In all but two cases, that
> will result in varchar(1). The two exceptions are inside CAST and CONVERT
> where you get varchar(30). This is why you see a length of 30 for this
> column.
Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default length consistent across all methods?|||> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at least make the
default
> length consistent across all methods?
I wish it would be deprecated, Aaron. We all se how much trouble it causes.
I'd prefer an error if
omitting length. Next best would be consistent length of 1 (easier to catch
the mistakes).
I checked ANSI SQL a while back, and for char, you should get 1 if you omit.
But for varchar, you
have to specify a length. (http://www.karaszi.com/SQLServer/in.../>
tatypes.asp)
I haven't vented this with MS, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e6o6KfQTGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hey Tibor, do you think they will ever deprecate this silly syntax? Or at
least make the default
> length consistent across all methods?
>