SP Question

15 Jul 2005 7:56 PM

tim

Hi,

I am somewhat new to T-SQL and I am this error The column prefix 'table1'
does not match with a table name or alias name used in the query. What is
wrong with the code below?

SELECT fmtno, fmtr INTO table1 FROM fomtg WHERE fmtno = @ptNo
SET @rowCount = @@ROWCOUNT

IF @rowCount > 0
BEGIN

IF table1.fmtr <> @pmtr
BEGIN
SELECT * FROM fm
END
END

15 Jul 2005 8:17 PM

Alejandro Mesa

Tim,

You can not reference a table's column directly, out of a select statement.

> IF table1.fmtr <> @pmtr

if (SELECT fmtr FROM fomtg WHERE fmtno = @ptNo) != @pmtr
    begin
    ...
    end

AMB

Show quote

"tim" wrote:

> Hi,
>
> I am somewhat new to T-SQL and I am this error The column prefix 'table1'
> does not match with a table name or alias name used in the query. What is
> wrong with the code below?
>
> SELECT fmtno, fmtr INTO table1 FROM fomtg WHERE fmtno = @ptNo
> SET @rowCount = @@ROWCOUNT
>
> IF @rowCount > 0
> BEGIN
>
> IF table1.fmtr <> @pmtr
> BEGIN
> SELECT * FROM fm
> END
> END
>
>
>

15 Jul 2005 8:20 PM

Aaron Bertrand [SQL Server MVP]

> if (SELECT fmtr FROM fomtg WHERE fmtno = @ptNo) != @pmtr

Of course, this will only work if there is only one row in fomtg... I am not
convinced that fmtno is a key.

I don't know who could possibly work with this naming scheme and stay sane,
BTW.

15 Jul 2005 8:19 PM

Aaron Bertrand [SQL Server MVP]

Did you mean to run a query, like:

IF EXISTS (SELECT 1 FROM table1 WHERE fmtr <> @pmtr)
SELECT * FROM fm

??