Reference Columns in Table Parameter 1

[Auguy]

I have this as a parameter in a stored procedure.

@Tvp Tvp_UserMasterSearch READONLY

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

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

 

[Olaf Doschke]

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:

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

 

[Auguy]

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

 

[Olaf Doschke]

If you know that you can define, for example:

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

 

[Auguy]

Thanks Olaf, you are always helpful!

Auguy
Sylvania/Toledo Ohio