来自两个表之一的 SQL 数据 [英] SQL Data from One of Two Tables

问题描述

我有两张表，其中只有一列是相同的.我正在尝试编写一个存储过程，它从传入的列名中提取一个值，但它只存在于其中一个表中.这是我写的:

I have two tables, where only one column is the same. I am trying to write a stored procedure which pulls a value from a passed-in column name, but one which only exists in one of the tables. This is what I have written:

IF EXISTS(
SELECT identifier FROM TableA WHERE identifier='12345')
SELECT ColumnWhichOnlyExistsInA FROM TableA WHERE identifier='12345'
ELSE
SELECT ColumnWhichOnlyExistsInA FROM TableB WHERE identifier='12345'

这在最后一行给了我无效的列名"错误，即使 IF 语句是正确的并且它应该只从 TableA 中提取内容.我注意到如果我用做同样事情的存储过程替换底部的两个 SELECT 语句，它会起作用；SQL 不会尝试执行 IF 为假的那个.

This gives me the 'Invalid column name' error on the last line, even though the IF statement is true and it should only be pulling stuff from TableA. I've noticed that if I replace the bottom two SELECT statements with stored procedures which do the same thing, it works; SQL doesn't try to execute the one where IF is false.

我想将所有内容都保存在一个存储过程中.最好的方法是什么?

I want to keep everything in one stored procedure. What would be the best way of doing it?

推荐答案

您的查询无法编译，因为编译器检查要选择的列是否存在于相应的表中 - 因此 IF 语句永远不会执行.

Your query is failing to COMPILE because the compiler checks that columns to be selected exist in the respective tables - so the IF statement never gets executed.

它在单独的存储过程中工作，因为它们是在运行时按需编译的，因此只会编译工作"存储过程.

It works in separate stored procedures because these are compiled on demand at runtime, and thus only the "working" stored proc would be compiled.

要解决这个问题，请使用动态 SQL

To get around the problem use Dynamic SQL

 DECLARE @SQLString varchar(max)
 IF EXISTS (SELECT idenfier FROM TableA WHERE identifier='12345')
 BEGIN
    SET @SQLString = 'SELECT ColumnWhichOnlyExistsInA FROM TableA WHERE identifier=''12345'''
 END
 ELSE
 BEGIN
    SET @SQLString = 'SELECT ColumnWhichOnlyExistsInA FROM TableB WHERE identifier=''12345'''
 END

 EXECUTE sp_executesql @SQLString

这篇关于来自两个表之一的 SQL 数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！