Invalid Object Name?

Hi

I am developing a windows application that connects to a sql 2000 server. I have created a stored procedure and I am trying to execute the stored procedure in the query analyzer.

Here is the code for the stored procedure

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

Select count([@.dlname].[Auditors working code])
from [@.dlname]
where [Auditors working code] = @.CB
GO

Here is the code that I am attempting to use in the query analyzer

Declare @.dlname as nvarchar(25)
set @.dlname = 'SWMC-OP-02-2005'
Declare @.CB as nvarchar(10)
set @.CB = 'CB'
Execute CountofComebacks @.dlname, @.CB

I get the following error in the query analyzer

Server: Msg 208, Level 16, State 1, Procedure CountofComebacks, Line 9
Invalid object name'@.dlname'.

Any assistance would be greatly appreciated.

thanks

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10)
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)
GO

If you want to know more about how this works google for Dynamic SQL. There are disadvantages in using this approach too. there are plenty of articles over the net that xplain about "Dynamic SQL".

|||

First let me say thank you for your response.

That being said I am still getting an error when attempting to execute the sp in the query analyzer.

Here is the code in the query analyzer

Declare @.dlname as nvarchar
Set @.dlname = 'SWMCOP022005'
Declare @.CB as nvarchar
Set @.CB = 'CB'
Declare @.NumberofCBs as integer
Execute CountofComebacks @.dlname, @.CB, @.NumberofCBs

Here is the sp

CREATE PROCEDURE dbo.CountofComebacks
(
@.dlname as nvarchar(25),
@.CB as nvarchar(10),
@.NumberofCBs as int
)

AS

DECLARE @.sql varchar(1000)

SET @.sql = 'Select count([' + @.dlname + '].[Auditors working code])
from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

EXEC(@.sql)

return @.NumberofCBs
GO

and here is the error I receive in the query analyzer

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '='.
The 'CountofComebacks' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

Is this because of the syntax error on line 2?

thanks

|||

throw in an ISNULL function.

SET @.sql = 'Select ISNULL(count([' + @.dlname + '].[Auditors working code]),0) from ' + @.dlname + 'where [Auditors working code] = ' + @.CB

sql