×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Reference Columns in Table Parameter

Reference Columns in Table Parameter

Reference Columns in Table Parameter

(OP)
I have this as a parameter in a stored procedure.

CODE

@Tvp Tvp_UserMasterSearch READONLY 
Is there a way to reference columns in @Tvp like this?

CODE

If @Tvp.UserID IS NOT NULL 
I've read that you have to copy to a temp table, but I still couldn't reference any columns. I want to pass in some search values without making each one a parameter in the proc. Any help would be appreciated.

Auguy
Sylvania/Toledo Ohio

RE: Reference Columns in Table Parameter

Well, what you do with tables is query from them, and in the query you can give a table an alias, this also works for table valued parameters, so what you can do is:

CODE

SELECT * FROM @TVP as users WHERE users.userid IS NOT NULL 

There's no such concept as accessing a column standalon in an IF. @Tvp is a table, not a record, even if the table has one record only.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Reference Columns in Table Parameter

(OP)
Thanks Olaf. The table I'm passing has only one record with a few columns like UserID, UserType, etc. Is there a way to get to that first row and then reference the column values?

Auguy
Sylvania/Toledo Ohio

RE: Reference Columns in Table Parameter

If you know that you can define, for example:

CODE -->

DECLARE @userid as int
SELECT @userid = userid FROM @TVP 

With multiple records you get the last rows userid stored into the variable, but with just one row in @tvp this will do, even without aliasing it. But @tvp never is just a reference to rows, it always is a reference to the whole table. You don't have the concept of SQL queries table.column outside of queries.

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Reference Columns in Table Parameter

(OP)
Thanks Olaf, you are always helpful!

Auguy
Sylvania/Toledo Ohio

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close