January 4, 2013 at 8:27 am
I dont quite understand the error I am getting here. if you run the query without using the set, it works fine. If you run with the set and exec you receive "'list_cte' has fewer columns that were specified in the column list'. Ideas?
DECLARE @sql VARCHAR(max)
SET @sql = '
; WITH list_cte (table_name,field_name,fld_id,sql_value,field_value,exporttype)
AS
(
SELECT table_name,field_name,fld_id,CAST('' AS VARCHAR(8000)) as sql_value, CAST('' AS VARCHAR(8000)) AS field_value,exporttype
FROM #configdetail
WHERE LEN(LTRIM(RTRIM(table_name))) > 0
)
select * from list_cte '
EXEC(@sql)
January 4, 2013 at 8:31 am
The problem is with the ' in the casts
If you select out the @sql param you will see that it is
; WITH list_cte (table_name,field_name,fld_id,sql_value,field_value,exporttype) AS
( SELECT table_name,field_name,fld_id,CAST(' AS VARCHAR(8000)) as sql_value, CAST(' AS VARCHAR(8000))
AS field_value,exporttype FROM #configdetail
WHERE LEN(LTRIM(RTRIM(table_name))) > 0 ) select * from list_cte
So your effectivly missing a column, as the string is stopped then started, your not actually putting in a blank string in the cast.
You need to use 4 ' in order to do what you want to do.
DECLARE @sql VARCHAR(max)
SET @sql = '
; WITH list_cte (table_name,field_name,fld_id,sql_value,field_value,exporttype)
AS
(
SELECT table_name,field_name,fld_id,CAST('''' AS VARCHAR(8000)) as sql_value, CAST('''' AS VARCHAR(8000)) AS field_value,exporttype
FROM #configdetail
WHERE LEN(LTRIM(RTRIM(table_name))) > 0
)
select * from list_cte '
EXEC(@sql)
Yesterdays main tech article was on the subject
January 4, 2013 at 8:37 am
Not enough single quotes in your dynamic sql:
DECLARE @sql VARCHAR(max)
SET @sql = '
; WITH list_cte (table_name,field_name,fld_id,sql_value,field_value,exporttype)
AS
(
SELECT table_name,field_name,fld_id,CAST('''' AS VARCHAR(8000)) as sql_value, CAST('''' AS VARCHAR(8000)) AS field_value,exporttype
FROM #configdetail
WHERE LEN(LTRIM(RTRIM(table_name))) > 0
)
select * from list_cte '
SELECT @sql;
EXEC(@sql) ;
January 4, 2013 at 8:38 am
Sweet..that worked. Thanks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply