Working with IF/ELSE and inserting into temp tables

Question

I'm trying to write a SQL query that accepts two inputs (a content ID and a content type), populates a temp table from different source tables depending on what the user sets as content type, and then finally runs a query against the temp table. I think I'm getting stuck on incorporating my temp table into my IF/ELSE statements.

So my question is, why does this return some non-zero number of results:

DECLARE @contentID int, @contenttype varchar
SET @contentid = 28861
SET @contenttype = 'resource'

DECLARE @tags_current TABLE (contentID int, taxonomyID int, isPrimary int)

INSERT INTO @tags_current (contentID, taxonomyID, isPrimary)
SELECT resourceID as contentID, taxonomyID, isPrimary
FROM resource2taxonomy r2t
WHERE r2t.resourceID = @contentID

SELECT * FROM @tags_current

Whereas this returns zero results:

DECLARE @contentID int, @contenttype varchar
SET @contentid = 28861
SET @contenttype = 'resource'

DECLARE @tags_current TABLE (contentID int, taxonomyID int, isPrimary int)

IF (@contenttype = 'resource')
BEGIN
    INSERT INTO @tags_current (contentID, taxonomyID, isPrimary)
    SELECT resourceID as contentID, taxonomyID, isPrimary
    FROM resource2taxonomy r2t
    WHERE r2t.resourceID = @contentID
END

SELECT * FROM @tags_current

Also, I've tried this, and it also returns zero results:

DECLARE @contentID int, @contenttype varchar, @command varchar
SET @contentid = 28861
SET @contenttype = 'resource'

DECLARE @tags_current TABLE (contentID int, taxonomyID int, isPrimary int)

IF (@contenttype = 'resource')
BEGIN
    SET @command = 'INTO @tags_current (contentID, taxonomyID, isPrimary) SELECT resourceID as contentID, taxonomyID, isPrimary FROM resource2taxonomy r2t WHERE r2t.resourceID = @contentID'
END

EXECUTE(@command)
SELECT * FROM @tags_current

I've read up on this, this, and this, but for some reason it's just not clicking for me. I'm using MS SQL Server 2014, in case that helps. Here's the SQL Fiddle. Thanks!

score 5 · Accepted Answer · answered Sep 04 '15 at 17:33

5

This is actually very simple, you aren't giving a length to @contenttype, as such, by default it's taking a length of 1 char.

This will be clear if you do this:

DECLARE @contentID int, @contenttype varchar
SET @contentid = 28861
SET @contenttype = 'resource'
SELECT @contenttype

The result is r, so, IF (@contenttype = 'resource') is not true

answered Sep 04 '15 at 17:33

Lamak

69,480
12
108
116

Thank you - this is the answer. It's too soon for me to accept it as the answer (cannot accept an answer within 10 minutes), but I will come back and do that. – Scott Sep 04 '15 at 17:36

Working with IF/ELSE and inserting into temp tables

1 Answers1