Easy Query Syntax Question

Good morning, experts.
Got a syntactical snag I need a fresh pair of eyes on. I've looked at this
query too long and I know that the fix is probably staring me in the face bu
t
I can't see the "forest for the trees", as it were.
SELECT * FROM
((SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
INNER JOIN
(SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
INNER JOIN
(SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY') RF
ON NM.NM1_ID = RF.NM1_ID
I get a "Invalid syntax near CS, NM and RF" error when I try to execute the
whole thing. I'm betting I just need to throw some open/close parens in
there somewhere but not sure where. If anybody could help me tweak this
thing so I can get it to parse I'd be most grateful.Try,
SELECT * FROM
(
select *
from
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
) OrgNtc
INNER JOIN
(
SELECT XH.CDS_04
, XH.CDS_07
, XH.CDS_09
, CH.CaseHdr_ID
FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
ON XH.XActHdr_ID = CH.XActHdr_ID
WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
) DsmNtc
ON OrgNtc.CDS_04 = DsmNtc.CDS_04
AND OrgNtc.CDS_07 = DsmNtc.CDS_07
AND OrgNtc.CDS_09 = DsmNtc.CDS_09
) as CS
INNER JOIN
(
SELECT CaseHdr_ID, NM1_ID
FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
) as NM
ON CS.CaseHdr_ID = NM.CaseHdr_ID
INNER JOIN
(
SELECT REF_02, NM1_ID
FROM EBN_REF WHERE REF_01 = 'SY'
) as RF
ON NM.NM1_ID = RF.NM1_ID
AMB
"Bob St. Aubyn" wrote:

> Good morning, experts.
> Got a syntactical snag I need a fresh pair of eyes on. I've looked at thi
s
> query too long and I know that the fix is probably staring me in the face
but
> I can't see the "forest for the trees", as it were.
> SELECT * FROM
> ((SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30) OrgNtc
> INNER JOIN
> (SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09) CS
> INNER JOIN
> (SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')) NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY') RF
> ON NM.NM1_ID = RF.NM1_ID
> I get a "Invalid syntax near CS, NM and RF" error when I try to execute th
e
> whole thing. I'm betting I just need to throw some open/close parens in
> there somewhere but not sure where. If anybody could help me tweak this
> thing so I can get it to parse I'd be most grateful.|||Thanks Alejandro. Worked and made sense.
I had to make one slight modification to get it to parse: Changed the new
"select * from" (in lower case) to "SELECT OrgNtc.CDS_04, OrgNtc.CaseHdr_ID
FROM ..." because both of those columns are selected in each of the
subqueries combined to make CS. Just had to explicitly specify which one I
wanted to return.
"Alejandro Mesa" wrote:
> Try,
> SELECT * FROM
> (
> select *
> from
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 30\
> ) OrgNtc
> INNER JOIN
> (
> SELECT XH.CDS_04
> , XH.CDS_07
> , XH.CDS_09
> , CH.CaseHdr_ID
> FROM EBN_XActHeader XH INNER JOIN EBN_CaseHeader CH
> ON XH.XActHdr_ID = CH.XActHdr_ID
> WHERE CH.CED_01 = 'N' AND CH.CASE_STATUS = 15
> ) DsmNtc
> ON OrgNtc.CDS_04 = DsmNtc.CDS_04
> AND OrgNtc.CDS_07 = DsmNtc.CDS_07
> AND OrgNtc.CDS_09 = DsmNtc.CDS_09
> ) as CS
> INNER JOIN
> (
> SELECT CaseHdr_ID, NM1_ID
> FROM EBN_NM1 WHERE NM1_01 IN ('XB', 'JT')
> ) as NM
> ON CS.CaseHdr_ID = NM.CaseHdr_ID
> INNER JOIN
> (
> SELECT REF_02, NM1_ID
> FROM EBN_REF WHERE REF_01 = 'SY'
> ) as RF
> ON NM.NM1_ID = RF.NM1_ID
>
> AMB
> "Bob St. Aubyn" wrote:
>

Dynamic table and column list

Thanks in advance (even CELKO) DDL attached
I DID NOT create this application. I am trying to fix it. The application
recieves a Access database table once a month. This table contains employee
s
and multiple columns detailing begining and ending balances, pay amounts
etc... The tables contain UP TO 240 columns per table The time periods rol
l
forward so some columns drop off as others are added. 3 years 36 tables.
TERRRIBLE NASTY MESS!!
I created a couple of tables that allowed me to ennumerate all the tables
(manually) and all the columns in each table. I then grouped by column name
and created a table that will contain all the columns (total 388) so that I
can create one record for each employee.
I need to be able to insert/update the results table from each monthly table
without having to manually type each column list for the insert update.
I did read: http://www.sommarskog.se/dynamic_sql.html
But I'm thinking I may need to do something to loop through the
IMPORT_MASTER to run against each tables columns
CREATE TABLE [dbo].[IMPORT_MASTER] (
[DataFileID] [int] IDENTITY (1, 1) NOT NULL ,
[FileName] [varchar] (50) NULL ,
[DateRecieved] [datetime] NOT NULL ,
[DateProcessed] [datetime] NULL
) ON [PRIMARY]
GO
INSERT IMPORT_MASTER Values ('masterfile_011604','2004-01-16
00:00:00.000',NULL)
INSERT IMPORT_MASTER Values ('masterfile_022004','2004-02-20 00:00:00.000',
NULL)
INSERT IMPORT_MASTER Values ('masterfile_032004','2004-03-20 00:00:00.000',
NULL)
CREATE TABLE [dbo].[IMPORT_Process] (
[ColumnKeyID] [int] IDENTITY (1, 1) NOT NULL ,
[DataFileID] [int] NOT NULL ,
[ColumnName] [varchar] (50) NULL ,
[ColumnProcess] [varchar] (50) NULL
) ON [PRIMARY]
GO
INSERT IMPORT_PROCESS VALUES (1,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (1,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col1',NULL)
INSERT IMPORT_PROCESS VALUES (1,'Col2',Null)
INSERT IMPORT_PROCESS VALUES (1,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (1,'MCol1',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol2',Null)
INSERT IMPORT_PROCESS VALUES (1,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (2,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col2',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (2,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'EmployeeID',NULL)
INSERT IMPORT_PROCESS VALUES (3,'autopay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'AutoSelfPay',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'Col8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol3',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol4',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol5',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol6',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol7',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol8',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol9',NULL)
INSERT IMPORT_PROCESS VALUES (3,'MCol10',NULL)
CREATE TABLE [dbo].[IMPORT_TRANSACTION] (
[ImportTransID] [int] IDENTITY (1, 1) NOT NULL ,
[DataFileID] [int] NOT NULL ,
[EmployeeID] [INT] NOT NULL ,
[familyid] [varchar] (50) NULL ,
[firstname] [varchar] (50) NULL ,
[lastname] [varchar] (50) NULL ,
[autopay] [bit] NOT NULL ,
[autoselfpay] [bit] NOT NULL ,
[AutoStartART] [money] NULL ,
[Col1] [money] NULL ,
[Col2] [money] NULL ,
[Col3] [money] NULL ,
[Col4] [money] NULL ,
[Col5] [money] NULL ,
[Col6] [money] NULL ,
[Col7] [money] NULL ,
[Col8] [money] NULL ,
[Col9] [money] NULL ,
[MCol1] [money] NULL ,
[MCol2] [money] NULL ,
[MCol3] [money] NULL ,
[MCol4] [money] NULL ,
[MCol5] [money] NULL ,
[MCol6] [money] NULL ,
[MCol7] [money] NULL ,
[MCol8] [money] NULL ,
[MCol9] [money] NULL ,
[MCol10] [money] NULL
) ON [PRIMARY]
GOStvJston wrote:

> Thanks in advance (even CELKO) DDL attached
> I DID NOT create this application. I am trying to fix it. The applicatio
n
> recieves a Access database table once a month. This table contains employ
ees
> and multiple columns detailing begining and ending balances, pay amounts
> etc... The tables contain UP TO 240 columns per table The time periods r
oll
> forward so some columns drop off as others are added. 3 years 36 tables.
> TERRRIBLE NASTY MESS!!
> I created a couple of tables that allowed me to ennumerate all the tables
> (manually) and all the columns in each table. I then grouped by column na
me
> and created a table that will contain all the columns (total 388) so that
I
> can create one record for each employee.
> I need to be able to insert/update the results table from each monthly tab
le
> without having to manually type each column list for the insert update.
> I did read: http://www.sommarskog.se/dynamic_sql.html
>
You say you want to fix it but I don't understand what fixing it has to
do with what you are attempting here. Here's what I'd assume: 1) Create
a normalized data model in SQL. 2) Create a data loading process that
populates the new data model from the Access one.
For 2) the obvious solution is DTS, in which transformations can map
columns and tables dynamically to your new model. Even assuming you
wanted to implement that in TSQL only (using linked servers?) I don't
see how your metadata tables will help much. A more standard approach
would be to use staging tables that matched the source structure and
then transform those staging tables to the normalized ones using
INSERTs. Maybe you could explain a bit more about what you are trying
to achieve.

> But I'm thinking I may need to do something to loop through the
> IMPORT_MASTER to run against each tables columns
Why? But if so, use a WHILE loop.
David Portas
SQL Server MVP
--|||David,
There is now no way to balance the system from one month to another. This
entire application model is going away but I have to try and produce a
balance sheet for manual work that was done the last three years to finish
nailing the coffin.. Entries hand entered, adjustments to balances made
etc... I hadn't thought about using DTS though This only needs to be done
one time so I'm trying to do it as quick and dirty as I can while still bein
g
able to document what I'm doing so the process could be replicated if needed
.
"David Portas" wrote:

> StvJston wrote:
>
> You say you want to fix it but I don't understand what fixing it has to
> do with what you are attempting here. Here's what I'd assume: 1) Create
> a normalized data model in SQL. 2) Create a data loading process that
> populates the new data model from the Access one.
> For 2) the obvious solution is DTS, in which transformations can map
> columns and tables dynamically to your new model. Even assuming you
> wanted to implement that in TSQL only (using linked servers?) I don't
> see how your metadata tables will help much. A more standard approach
> would be to use staging tables that matched the source structure and
> then transform those staging tables to the normalized ones using
> INSERTs. Maybe you could explain a bit more about what you are trying
> to achieve.
>
> Why? But if so, use a WHILE loop.
> --
> David Portas
> SQL Server MVP
> --
>|||StvJston (StvJston@.discussions.microsoft.com) writes:
> I DID NOT create this application. I am trying to fix it. The
> application recieves a Access database table once a month. This table
> contains employees and multiple columns detailing begining and ending
> balances, pay amounts etc... The tables contain UP TO 240 columns per
> table The time periods roll forward so some columns drop off as others
> are added. 3 years 36 tables. TERRRIBLE NASTY MESS!!
> I created a couple of tables that allowed me to ennumerate all the
> tables (manually) and all the columns in each table. I then grouped by
> column name and created a table that will contain all the columns (total
> 388) so that I can create one record for each employee.
> I need to be able to insert/update the results table from each monthly
> table without having to manually type each column list for the insert
> update.
I had a look at your tables, and I read your narrative, but I had
difficulties to bring it together. What is what? Unless you are taking
the DTS path that David suggested, it would help with more details.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Solved this. Created a DTS package that completed the task. Thanks for the
pointers
"Erland Sommarskog" wrote:

> StvJston (StvJston@.discussions.microsoft.com) writes:
> I had a look at your tables, and I read your narrative, but I had
> difficulties to bring it together. What is what? Unless you are taking
> the DTS path that David suggested, it would help with more details.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

dynamic sql to loop over fiscal years

thanks for reading.

i'm interested in improving the format of this query. consider me clueless today, if you will. :) how can i fix this to make it dynamically move over the years? is there something i can do with set manipulation that is smarter than this?

the goal of this query is to return cases per year, where "year" is defined as (Oct 1, YYYY - Sep 30, YYYY+1) instead of the typical YYYY

problem is, i have to write it as some cludgy dynamic sql looping over an incremented year. i don't know of any other way.

again, thanks for reading ... and any help in advance.

SELECT count(*) as 'Data Points', '2001' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2001' AND '10/01/2002'
and completion_date BETWEEN '9/30/2001' AND '10/01/2002'
and status = 'CaseClosed'

UNION

SELECT count(*) as 'Data Points', '2002' as 'Experiment Year'
FROM tbl_experiment_data

WHERE start_date BETWEEN '9/30/2002' AND '10/01/2003'
and completion_date BETWEEN '9/30/2002' AND '10/01/2003'
and status = 'CaseClosed'

UNION

...

expected output...

Data Points______ Experiment Year
32_____________ 2001
102____________ 2002
... ...Create a table called ExperimentYears, populate it with ExperimentYear char(4), YearStart datetime, YearEnd datetime. Then do just one SELECT similar to yours:

SELECT count(*) as [Data Points], ExperimentYear
from tbl_experiment_data ted
inner join ExperimentYears ey
on ted.start_date between ey.YearStart and ey.YearEnd
and ted.completion_date between ey.YearStart and ey.YearEnd
where ted.status = 'CaseClosed'
group by ey.ExperimentYear|||We use a separate calendar table. something like this:

create table FiscalCalendar
(FiscalYear int,
StartDate datetime,
EndDate datetime)

This should reduce your query to something like

select count(*), fc.FiscalYear
from tbl_experiment_data a, FiscalCalendar fc
where a.startdate between fc.startdate and fc.enddate
and a.enddate between fc.startdate and fc.enddate
group by fc.fiscalyear

Been a while since I messed with this, so experiment with this for a bit. As a curiosity, what happens to experiments that start in one fiscal year and end in the next?|||as i'm re-reading my post now i can see that maybe it wouldn't even work as is because i have the count(*) without a 'group by'

still, i hope these sorts of mistakes can be overlooked as i ask for help.

it also occurred to me just now that maybe i could use a user-defined function that returns the value of the year as redefined by the "year" range above.

that way i could rewrite the query like this...

==============
SELECT count(*) as 'Data Points', getFiscalYear(start_date) as 'Experiment Date'
FROM tbl_experiment_data

WHERE DATEPART(YEAR, start_date) = getFiscalYear(start_date)
and DATEPART(YEAR, completion_date) = getFiscalYear(completion_date) and status = 'CaseClosed'

GROUP BY getFiscalYear(start_date)

ORDER BY getFiscalYear(start_date)

==============

any comments? criticisms? other ideas?

thanks again for reading ... and your input

oh, great! i just noticed the responses now too. thank you. i'll try these ideas out.|||SELECT count(*) as 'Data Points',
year(dateadd(d, 92, StartDate))-1 as 'Experiment Year'
FROM tbl_experiment_data
WHERE status = 'CaseClosed'

The year(dateadd(d, 92, StartDate))-1 function returns the experiment year by addint 92 days (Oct +Nov +Dec) and then subtracting 1 from the year. Note that if you just subtracted days you would have to account for leap years.

You will need to decide what to do if an experiment starts in one year and ends in the next. Your original code would skip those instances entirely.

Dynamic SQL Help

I am getting the following error when trying to use dynamic sql. I've tried
several ways to fix it, but have not been able to figure it out. Can someone
show me how to fix this? Thanks in advance for any help.
Error for the following stored procedure:
Invalid operator for data type. Operator equals add, type equals text.
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
EXEC sp_executesql @.sqlTry,
SET @.sql = N'
DECLARE @.ptrVal binary(16)
SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
FROM Table1 WHERE TableID = @.TableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
go
AMB
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||TEXT datatype cannot be used with '+' operator, try changing @.fieldText to
varchar()
- Sha Anand
"Mike Collins" wrote:

> I am getting the following error when trying to use dynamic sql. I've trie
d
> several ways to fix it, but have not been able to figure it out. Can someo
ne
> show me how to fix this? Thanks in advance for any help.
> Error for the following stored procedure:
> Invalid operator for data type. Operator equals add, type equals text.
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = ''' + CAST(@.tableID AS varchar(40)) + '''
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal ''' + @.fieldText + ''''
> EXEC sp_executesql @.sql
>|||Hello, Mike
You can (but shoudn't) use something like this:
ALTER PROC UpdateTable1Field(
@.tableID uniqueidentifier,
@.fieldUpdated varchar(50),
@.fieldText text)
AS
DECLARE @.sql nvarchar(4000)
SET @.sql = N'DECLARE @.ptrVal binary(16)
SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
') FROM Table1 WHERE TableID = @.tableID
WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
@.tableID, @.fieldText
However, you should know that:
1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
in that text column (i.e. it was not NULL), before executing the
procedure.
2. Dynamic SQL is usually a bad idea. If a stored procedure contains
Dynamic SQL, a lot of the reasons for using a stored procedure
(security, performance, maintanability) are now voided. Read the
following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/dynamic_sql.html
especially the part about "Common Cases when to (Not) Use Dynamic SQL".
Razvan|||Thanks, that works except I need to figure out what the following error mean
s.
NULL textptr (text, ntext, or image pointer) passed to WriteText function.
"Alejandro Mesa" wrote:
> Try,
> SET @.sql = N'
> DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal = TEXTPTR(' + @.fieldUpdated + N')
> FROM Table1 WHERE TableID = @.TableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||What about when my data is too long for a varchar data type? Although not in
all, in many cases it will be too long.
"Sha Anand" wrote:
> TEXT datatype cannot be used with '+' operator, try changing @.fieldText
to
> varchar()
> - Sha Anand
> "Mike Collins" wrote:
>|||Thanks...will this article point me to an alternative to what I am trying to
do? I will not know the column to update ahead of time.
"Razvan Socol" wrote:

> Hello, Mike
> You can (but shoudn't) use something like this:
> ALTER PROC UpdateTable1Field(
> @.tableID uniqueidentifier,
> @.fieldUpdated varchar(50),
> @.fieldText text)
> AS
> DECLARE @.sql nvarchar(4000)
> SET @.sql = N'DECLARE @.ptrVal binary(16)
> SELECT @.ptrVal=TEXTPTR(' + @.fieldUpdated +
> ') FROM Table1 WHERE TableID = @.tableID
> WRITETEXT Table1.' + @.fieldUpdated + ' @.ptrVal @.fieldText'
> EXEC sp_executesql @.sql, N'@.tableID uniqueidentifier, @.fieldText text',
> @.tableID, @.fieldText
> However, you should know that:
> 1. Using TEXTPTR and WRITETEXT this way assumes that there was a value
> in that text column (i.e. it was not NULL), before executing the
> procedure.
> 2. Dynamic SQL is usually a bad idea. If a stored procedure contains
> Dynamic SQL, a lot of the reasons for using a stored procedure
> (security, performance, maintanability) are now voided. Read the
> following article by Erland Sommarskog, SQL Server MVP:
> http://www.sommarskog.se/dynamic_sql.html
> especially the part about "Common Cases when to (Not) Use Dynamic SQL".
> Razvan
>|||Mike,
The error is because the column was not initialized. Put some value or empty
string when you insert the row. See WRITETEXT in BOL for more info.
create table dbo.t1 (
c1 int not null identity,
c2 text
)
go
create procedure dbo.p1
@.c1 int,
@.c2 text
as
set nocount on
declare @.sql nvarchar(4000)
SET @.sql = N'
DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(c2)
FROM dbo.t1 WHERE c1 = @.c1
WRITETEXT dbo.t1.c2 @.ptrval @.c2'
EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
go
insert into dbo.t1(c2) values('uno')
go
exec dbo.p1 1, 'dos'
go
select * from dbo.t1
go
drop procedure dbo.p1
go
drop table dbo.t1
go
AMB
"Mike Collins" wrote:
> Thanks, that works except I need to figure out what the following error me
ans.
> NULL textptr (text, ntext, or image pointer) passed to WriteText function.
> "Alejandro Mesa" wrote:
>|||Thanks...it works great now.
"Alejandro Mesa" wrote:
> Mike,
> The error is because the column was not initialized. Put some value or emp
ty
> string when you insert the row. See WRITETEXT in BOL for more info.
> create table dbo.t1 (
> c1 int not null identity,
> c2 text
> )
> go
> create procedure dbo.p1
> @.c1 int,
> @.c2 text
> as
> set nocount on
> declare @.sql nvarchar(4000)
> SET @.sql = N'
> DECLARE @.ptrval binary(16)
> SELECT @.ptrval = TEXTPTR(c2)
> FROM dbo.t1 WHERE c1 = @.c1
> WRITETEXT dbo.t1.c2 @.ptrval @.c2'
> EXEC sp_executesql @.sql, N'@.c1 int, @.c2 text', @.c1, @.c2
> go
> insert into dbo.t1(c2) values('uno')
> go
> exec dbo.p1 1, 'dos'
> go
> select * from dbo.t1
> go
> drop procedure dbo.p1
> go
> drop table dbo.t1
> go
>
> AMB
>
> "Mike Collins" wrote:
>|||Mike Collins (MikeCollins@.discussions.microsoft.com) writes:
> Thanks...will this article point me to an alternative to what I am
> trying to do? I will not know the column to update ahead of time.
Yes, exactly this case is covered in the article.
And if you don't know the column to update ahead of time, there is
some fishy going on anyway. A stored procedure in a well-designed
data model would always operate on known tables and known columns.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx