Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Server 2008 - Using a ''User-Defined Table Type"

Status
Not open for further replies.

DayLaborer

Programmer
Jan 3, 2006
347
US
I have a proc using a "User-Defined Table Type" that compiles and SQL Server saves it, but when I open the proc (i.e. "Modify" the proc from SQL Server Management Studio), I see errors / warnings on it:
Code:
ALTER PROCEDURE [dbo].[spCompanyProfile_GetAuthenticationTypeByIvrPhoneNumberDialed]
(
	@IvrPhoneNumberDialed varchar(10),
	[COLOR=red][b]@AuthCredentialFromUser dbo.AuthenticationCredentialsFromCaller READONLY[/b][/color]
)
AS
BEGIN
	SET NOCOUNT ON;

[COLOR=green][b]--body of the proc is here[/b][/color]

[COLOR=red][b]select * from @AuthCredentialFromUser[/b][/color]

GO

SQL Server Management Studio shows me red-squiggly lines under both of the bold, red lines above. On the first line, it tells me (when I mouse-over the red-squiggly on the first half of the line):
[tt]The parameter "@AuthCredentialFromUser" can not be declared READONLY since it is not a table-valued parameter"[/tt]
...and for the second half of the line:
[tt]"Parameter of variable '@AuthCredentialFromUser' has an invalid data type."[/tt]

The red-squiggly for the second red, bold line says:
[tt]Must declare the table variable "@AuthCredentialFromUser".[/tt]

Here's the definition of my user-defined table type:
Code:
CREATE TYPE [dbo].[AuthenticationCredentialsFromCaller] AS TABLE(
	[AuthenticationValueFromUser] [varchar](50) NULL
)
I don't understand these error messages; they almost seem contradictory. Please advise.

Thanks so much,
Eliezer
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top