Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
SELECT does not find record
Message
 
 
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
01682735
Message ID:
01682762
Views:
25
Kevin,
Thank you for your detailed message. I will consider making the change in my Stored Procedure.
And I understand that you suggest a better practice.
Here is why I am reluctant.
You said that if the SQL Select selects more than 1 row and I use your suggested syntax, an error occurs.
I am concerned of having two many errors. Let me give you an example.
A user submits an order (from an ASP.NET application) and he/she enters his/her email. In the stored procedure I want to assign this order to this user; using user_pk. So, in the stored procedure I do a SQL Select by the email address find the user so that his/her PK can be assigned to the order.
But it may happen that more than one user would have the same email address. I deal with fairly large organizations where people often share the email. For whatever reasons; maybe to share the work discussions; or maybe some other reasons.
So, my SQL Select by the email address will trigger an error.
Using my syntax
Select @UserPK = User_pk from UserTable where @PassedEmail = EMAIL_ADDR
I realize it is possible that the "wrong" record is selected. So the order is assigned to the "wrong" user. But this is not super important for the application. And I can always explain to the customer why it happened. And important for me: no error.
This is the reason.

>>So, if I needed to get more than one value from the table into more than one variable, how would you do it?
>
>If I have two variables I want to populate, *and* if I know the query is optimized, I've done this.
>
>SET @var1 = (select somecolumn ...)
>SET @var2 = (select somecolumn ...)
>
>Again, when the intention is to query 1 row. Call it strong preference, but I don't like the SELECT @var = column approach.
>
>Now, if you start to ask, "what if you need to populate 5-6 variables, that means you'd be querying the same row again and again". Yes, that is true, though again, I would expect it to be optimized and you're likely pulling from the buffer pool.
>
>Having said that, it I wanted to populate 5-6 (or more) variables, then it sounds more like I'm wanting to pull a row structure - in that case, I'd use some basic result set mapping approach (like a .NET list/collection, ADO.NET data table, etc.) and wouldn't use variables.
>
>That's my way of saying, "If someone wants to pull all the columns into variables", I'd argue they need to be materializing those back in the application layer with some kind of basic list structure.
>
>Now, if someone wants to say, "I prefer the SELECT @var1 = column1, @var2 = column approach.....I'll guarantee in my query that I'll only pull one row, with a TOP 1 or some other mechanism", I can't really make a compelling argument against it.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform