February 12, 2019 at 9:38 am
I have a table with columns named like column1, column2, etc. all the way to column20 that are kind of acting like categories and then there are related data columns like column1a through column20a.
What I am trying to do is process each row in the table and deal with the data columns differently depending on the value or lack of a value in column1 - 20.
As an example, if Column1 is NULL, then I need to take the data in column1a and append it to @string, 1a would be the beginning of the string and column20a would be the end if it has a value based on column20 being NULL or not.
If Column1 is NOT NULL, then I do something completely different with column1a, but it would still tie into the process based on the values in the rest of the columns, in some cases, it will determine the length of substrings within @string.
Since all of the data in these columns is related and relies on what I've done with the previous columns, the nested IF...THEN...ELSE decision tree I started with became INSANE very quickly.
So I came up with an iterative process where I can with a reasonable amount of logic flow do what I need to do, but I need to increment the column# as a variable in a loop in order to do this, but that is where I run into problems.
I was trying this:
Set @TempSQL = 'SET @String = @String + @column' + (CAST(@LoopCount As Varchar(10)) + 'a')
Execute sp_sqlexec @TempSQL
SQL doesn't seem to like me using the SET command within dynamic SQL maybe?
Anyway my brain is fried at this point and I'm absolutely positive that I'm over thinking this and that there is an easier way. Any help would be greatly appreciated.
February 12, 2019 at 11:33 am
I see nothing here that argues for a dynamic approach. Post sample data and expected results, and we should be able to help you come up with an approach that doesn't require dynamic SQL.
The reason that you're getting an error message is that you haven't defined your variables @String and @column. The dynamic string has it's own scope, so it needs its own variable declarations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 12, 2019 at 12:00 pm
Maybe Kenneth Fisher's article on CROSS APPLY would help, but without some sample data, it's hard to tell.
February 12, 2019 at 5:09 pm
Thanks Drew,
My variables were declared, @Column wasn't supposed to be a variable though, that's a literal string and it looks like that's where I made my typo. I think that might solve my problem.
February 13, 2019 at 9:36 am
aforsythe - Tuesday, February 12, 2019 5:09 PMThanks Drew,My variables were declared, @Column wasn't supposed to be a variable though, that's a literal string and it looks like that's where I made my typo. I think that might solve my problem.
Here's the problem with that. Variables you declare within the SQL that is NOT part of your string variable are NOT the same ones that your executed string will have access to.... EVER. That can't happen. You can, however, parameterize your execution of the string variable by using sp_executesql. I don't recall the details, but a quick lookup of the documentation online will find that for you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 13, 2019 at 11:13 am
You can use a list of CROSS APPLYs to evaluate columns and concatenate values as you need to. The great thing is that new aliases ("columns") created in a CA are available to all CAs that follow it, and to the main SELECT statement above. For example:
SELECT string2, string1, tn.id
FROM dbo.table_name tn
CROSS APPLY (
SELECT CAST('' AS varchar(8000)) AS string /*create new string value to be concat'd into*/
) AS setup
CROSS APPLY (
SELECT string + CASE WHEN column1 IS NULL THEN column1a
ELSE SUBSTRING(column1, 3, 3) END AS string1
) AS alias1
CROSS APPLY (
SELECT string1 + CASE WHEN column2 IS NULL THEN column2a ELSE '' END AS string2
) AS alias2
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 15, 2019 at 5:55 pm
Thanks Scott.
I don't have the hang of cross apply yet, but I wasn't aware you could use CASE in select, I've only ever used it in my where clause. That right there solves one of the major headaches of this project and I think I can use that without the need for the CROSS APPLY.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply