This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C16D58.7181D6EE
Content-Type: text/plain

The only problem I can see with this is that there is no space between the
WHERE and the table name.

This sort of error should be easily debugged by testing it in query
analyser, as I did to find the error. See below:

DECLARE @strSQL1 varchar(1000), @tablename varChar(50)

SET @tablename = 'MyTable'

SELECT @strSQL1 = 'SELECT ION, CK_MO, CK_DATE, CK_YR, PYIND, Prod_State,
BBLS_MCF,
FRMO, FRYR, TOMO, TOYR FROM ' + @tablename + 
'WHERE (ISNumeric(ION) = 0) or (ISNumeric(CK_MO) = 0) or (ISNumeric(CK_DATE)
= 0) 
or (ISNumeric(CK_YR) = 0) or (ISNumeric(PYIND) = 0) or
(ISNumeric(Prod_State) = 0) 
or (ISNumeric(BBLS_MCF) = 0) or (ISNumeric(FRMO) = 0) or (ISNumeric(FRYR) 
0) 
or (ISNumeric(TOMO) = 0) or (ISNumeric(TOYR) = 0)'

SELECT @strSQL1

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: David Hodges [mailto:david.m.hodges.jr@a...]
Sent: Thursday, 15 November 2001 5:05 AM
To: sql language
Subject: [sql_language] Using table name as parameter in stored
procedure


I am trying to create a stored procedure using a table name as a 
parameter.  I am able to create simple statements like this but I am 
having trouble when I attach a where clause.  Here is the tsql I am using.

CREATE PROC dbo.TapeDataValidation
(@tablename varchar(25))
AS
DECLARE @strSQL1 nvarchar(1000)
SELECT @strSQL1 = 'SELECT ION, CK_MO, CK_DATE, CK_YR, PYIND, Prod_State, 
BBLS_MCF,
FRMO, FRYR, TOMO, TOYR FROM ' + @tablename + 
'WHERE (ISNumeric(ION) = 0) or (ISNumeric(CK_MO) = 0) or (ISNumeric
(CK_DATE) = 0) 
or (ISNumeric(CK_YR) = 0) or (ISNumeric(PYIND) = 0) or (ISNumeric
(Prod_State) = 0) 
or (ISNumeric(BBLS_MCF) = 0) or (ISNumeric(FRMO) = 0) or (ISNumeric(FRYR) 
= 0) 
or (ISNumeric(TOMO) = 0) or (ISNumeric(TOYR) = 0)'
EXEC sp_executesql @strSQL1

When I execute this procedure I get the following error message: Msg 170, 
Level 15, State 1, Line 2  Line 2: Incorrect syntax near '('.

I would appreciate any suggestions.  Thanks.