Returning multiple records when only one is specified

Question

Post reply

Returning multiple records when only one is specified

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 1

Ah i didnt know I could select like that.

Thanks Ben!!!

🙂

Just want to confirm one more thing with you, I can't put all this code into a view, and just query the view hey?

Because of all the options?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What's going to consume the result set from the view? How will it handle an unknown number of columns with some unknown names? Can't you concatenate the variable address elements into one address column called e.g. [address]?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 3

My boss wants this script as a view, and I told him you cant put this in a view, just wanted to make sure im right.

Thanks for your help man, I really appreciate it

BenWard SSCertifiable Points: 5903 More actions · Answer 4

Probably the best option if you need to preserve that variable number of output columns is to bung the lot into a stored procedure and use @NAMEID as a parameter.

http://msdn.microsoft.com/en-us/library/ms345415(v=sql.120).aspx

you'll then be able to execute it with something like

EXEC sprocFindAddress @NAMEID = 12345

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

crazy_new (2/5/2014)
My boss wants this script as a view, and I told him you cant put this in a view, just wanted to make sure im right.
Thanks for your help man, I really appreciate it

You need to know the answer to this: what's going to consume a result set with an unknown number of columns?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 6

crazy_new

SSCarpal Tunnel

Points: 4804

February 5, 2014 at 6:53 am

#1686183

I have no idea sorry

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Can't you ask your boss?

The reason I'm asking is because - if (s)he isn't aware that your script can't be shaped into a view, (s)he probably isn't aware that such a result set can be difficult or impossible to consume.

Are you absolutely sure that the "variable" columns aren't meant to be concatenated into one for output? It would solve so many future problems for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

crazy_new SSCarpal Tunnel Points: 4804 More actions · Answer 8

When he explained to me what he wants I did ask if i should have it in one column and he said no, im not sure even why he wants this.