Using a variable for a table in a SELECT statement which is in a IF statement

Question

I already searched for a solution, but the answers weren't specific enough to solve my problem.

So I have this Statement:

IF NOT EXISTS
(SELECT InternalRepositoryId FROM Tfs_GitInterface.dbo.tbl_GitRepository WHERE Name = @repositoryName)
BEGIN
    ...
END

or of course also possible:

IF NOT EXISTS
(SELECT InternalRepositoryId FROM dbo.tbl_GitRepository WHERE Name = @repositoryName)
BEGIN
    ...
END

Its working completely fine. I would like to mention that dbo is a Schema to another database. Now I want to make something like this:

DECLARE @schema [nvarchar](400);
SET @schema = 'dbo';

IF NOT EXISTS
(SELECT InternalRepositoryId FROM @schema.tbl_GitRepository WHERE Name = @repositoryName)
BEGIN
    ...
END

or

DECLARE @schemaTable [nvarchar](400);
SET @schemaTable = 'dbo.tbl_GitRepository';

IF NOT EXISTS
(SELECT InternalRepositoryId FROM @schemaTable WHERE Name = @repositoryName)
BEGIN
    ...
END

Now I tried already some dynamic sql but it wouldn't work without errors:

DECLARE @schemaTable [nvarchar](400);
SET @schemaTable = 'dbo.tbl_GitRepository';
DECLARE @sql [nvarchar](4000);
SET @sql = 'SELECT InternalRepositoryId FROM ' + @schemaTable + ' WHERE Name = ' + @repositoryName;

IF NOT EXISTS
(EXECUTE sp_executesql @sql)
BEGIN
    ...
END

Then I get the error at EXECUTE: "Incorrect Syntax near 'EXECUTE'. Expecting '(', or SELECT" and some minor Errors.

Does anyone got an idea how to do this?

EDIT: The IF statement was the big problem

Possible duplicate of [SQL "if exists..." dynamic query](http://stackoverflow.com/questions/27710260/sql-if-exists-dynamic-query) — Tanner, Oct 20 '16 at 14:58
that linked question seems like it will do what you are after. — Tanner, Oct 20 '16 at 14:58
I already tried something like this, but it didn't worked out :/ — Jordan Zapf, Oct 20 '16 at 15:56

score 1 · Answer 1 · answered Oct 20 '16 at 15:00

1

You will need to put the whole IF NOT EXISTS in your dynamic sql.

DECLARE @schemaTable [nvarchar](400);
SET @schemaTable = 'dbo.tbl_GitRepository';
DECLARE @sql [nvarchar](4000);
SET @sql = 'IF NOT EXISTS ( SELECT InternalRepositoryId FROM ' + @schemaTable + ' WHERE Name = ''' + @repositoryName + ''' )' + CHAR(10) +
'BEGIN SELECT 1' + CHAR(10) + ' END';

EXECUTE sp_executesql @sql

Where select 1 is where you put your actual statement

answered Oct 20 '16 at 15:00

JesalynOlson

503
3
9

I think using `@@ROWCOUNT` as suggested in the duplicate I've linked would be a nicer solution. – Tanner Oct 20 '16 at 15:03
To me this looks nicer, I try it. But for what do I need the CHAR(10)? – Jordan Zapf Oct 20 '16 at 15:07
Nvm I found CHAR(10) :) – Jordan Zapf Oct 20 '16 at 15:10

Using a variable for a table in a SELECT statement which is in a IF statement

1 Answers1