Parameter problem

I have this sproc:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[usp_CrimRecTest]

@.caseID nvarchar(12),
@.lastName nvarchar(25) output,
@.firstName nvarchar(20) output

AS
BEGIN
SET NOCOUNT ON;

SELECT dbo.tblCASENOMASTER.CASENO, dbo.tblCASEMAST.LASTNAME, dbo.tblCASEMAST.FRSTNAME
FROM dbo.tblCASENOMASTER LEFT OUTER JOIN
dbo.tblCASEMAST ON dbo.tblCASENOMASTER.CASENO = dbo.tblCASEMAST.CASENO
WHERE (dbo.tblCASENOMASTER.CASENO = @.caseID)

END

How do I get values into @.lastName and @.firstName? I have tried this:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME

I got this error:

Msg 141, Level 15, State 1, Procedure usp_CrimRecTest, Line 22
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Help!

Thanks.

Hi,

that a easy thing, like the error says, you can′t combine data retrieval (thats selecting or outputting the result *and* assigning values to variables. If you want to put out the *CASENO*, put in in a variable and do a SELECT @.CASENO afterwards fetching the values into the variables.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

This may be doing what Jens suggested, but I'm not sure. I changed:

SELECT dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

to this:

SELECT @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

and it worked.

Thanks for the reply Jens.

|||

Nice to hear that this helped, but if you want to select (output the case number, I am not sure after you last reply) you *could* do that:

SELECT @.Caseno = dbo.tblCASENOMASTER.CASENO, @.lastName = dbo.tblCASEMAST.LASTNAME, @.firstName = dbo.tblCASEMAST.FRSTNAME...

Select @.Caseno

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de