Posted by Roy Harvey on 02/25/07 13:44

Please post responses to the newsgroup, not to email. This promotes
the basic function of newsgroups, sharing information. It also means
that more than one person is reading and thinking about your problem.

If you did not add a WHERE clause test to limit the columns then I am
quite surprised that the UPDATE line did not appear in the output.
That would seem to indicate that there is no colid = 1 for the table.
I was able to create that condition by doing an ALTER TABLE to drop
the first column, but it is a condition my original query did not
allow for.

Did you try the alternate syntax I provided? What do you get from
this query?

SELECT MIN(colid), MAX(colod), count(colid), count(distinct colid)
FROM sysobjects O
JOIN syscolumns C
ON O.id = C.id
WHERE O.name = @tblname

As for executing the code from inside the stored procedure, it is
possible. The command is spread over many rows, so the first step is
to turn that query into a cursor and step through the rows
concatenating all of them into a single string. Then you would have
to use dynamic SQL to execute it. Before doing that I suggest reading
this article very carefully: http://www.sommarskog.se/dynamic_sql.html

Roy Harvey
Beacon Falls, CT

>Roy,
>
>Thank you for your time and helping me out. In my last reply the
>output I copied was wrong. Here is the code and output I get from your
>script:
>declare @tblname nvarchar(60)
>set @tblname = '_PHYSICIAN_PROFILE'
>
>SELECT CASE WHEN C.colid = 1
> THEN 'UPDATE ' + O.name + CHAR(13) + CHAR(10) +
> ' SET '
> ELSE ' '
> END +
> C.name + '= replace(cast(' + C.name +
> ' as nvarchar(255)),char(13)+char(10),'':'')' +
> CASE
> WHEN C.colid < (select max(colid) from syscolumns CC
> where O.id = CC.id)
> THEN ','
> ELSE ';'
> END
> FROM sysobjects O
> JOIN syscolumns C
> ON O.id = C.id
> WHERE O.name = @tblname
> ORDER BY C.id, C.colid
>
>======= output 42 rows =========
>
> _NAME= replace(cast(_NAME as
>nvarchar(255)),char(13)+char(10),':'),
> _SPECIALTY= replace(cast(_SPECIALTY as
>nvarchar(255)),char(13)+char(10),':'),
> _GENDER= replace(cast(_GENDER as
>nvarchar(255)),char(13)+char(10),':'),
> _SPECIAL_INTERESTS= replace(cast(_SPECIAL_INTERESTS as
>nvarchar(255)),char(13)+char(10),':'),
> _PRACTICE_HIGHLIGHTS= replace(cast(_PRACTICE_HIGHLIGHTS as
>nvarchar(255)),char(13)+char(10),':'),
> _TRAINING_POST_GRADUATE_EDUCATION=
>replace(cast(_TRAINING_POST_GRADUATE_EDUCATION as
>nvarchar(255)),char(13)+char(10),':'),
> _BOARD_CERTIFICATION= replace(cast(_BOARD_CERTIFICATION as
>nvarchar(255)),char(13)+char(10),':'),
> _LANGUAGES_SPOKEN= replace(cast(_LANGUAGES_SPOKEN as
>nvarchar(255)),char(13)+char(10),':'),
> _INSURANCE_ACCEPTED= replace(cast(_INSURANCE_ACCEPTED as
>nvarchar(255)),char(13)+char(10),':'),
> _PERSONAL_INFORMATION= replace(cast(_PERSONAL_INFORMATION as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS1_1= replace(cast(_ADDRESS1_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS1_2= replace(cast(_ADDRESS1_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS1_3= replace(cast(_ADDRESS1_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE1= replace(cast(_PHONE1 as
>nvarchar(255)),char(13)+char(10),':'),
> _FAX1= replace(cast(_FAX1 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK1= replace(cast(_IN_NETWORK1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS2_1= replace(cast(_ADDRESS2_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS2_2= replace(cast(_ADDRESS2_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS2_3= replace(cast(_ADDRESS2_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE2= replace(cast(_PHONE2 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK2= replace(cast(_IN_NETWORK2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS3_1= replace(cast(_ADDRESS3_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS3_2= replace(cast(_ADDRESS3_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS3_3= replace(cast(_ADDRESS3_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE3= replace(cast(_PHONE3 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK3= replace(cast(_IN_NETWORK3 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS4_1= replace(cast(_ADDRESS4_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS4_2= replace(cast(_ADDRESS4_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS4_3= replace(cast(_ADDRESS4_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE4= replace(cast(_PHONE4 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK4= replace(cast(_IN_NETWORK4 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS5_1= replace(cast(_ADDRESS5_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS5_2= replace(cast(_ADDRESS5_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS5_3= replace(cast(_ADDRESS5_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE5= replace(cast(_PHONE5 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK5= replace(cast(_IN_NETWORK5 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS6_1= replace(cast(_ADDRESS6_1 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS6_2= replace(cast(_ADDRESS6_2 as
>nvarchar(255)),char(13)+char(10),':'),
> _ADDRESS6_3= replace(cast(_ADDRESS6_3 as
>nvarchar(255)),char(13)+char(10),':'),
> _PHONE6= replace(cast(_PHONE6 as
>nvarchar(255)),char(13)+char(10),':'),
> _IN_NETWORK6= replace(cast(_IN_NETWORK6 as
>nvarchar(255)),char(13)+char(10),':'),
> META_SRC_URI= replace(cast(META_SRC_URI as
>nvarchar(255)),char(13)+char(10),':');
>
>I don't think I filter out anything yet I don't have UPDATE or SET
>function and the output from this query is just like a select
>statement that does not execute the replacement.
>
>Is there a way actually execute the replace from this stored procedure?

[Back to original message]