CODEWITHSUNDEEP
sql-serverstored-proceduresjoin
Csharp
Csharp

Reputation: 2982

Creating a Stored Procedure with if statement on Inner Join

I am trying to create a stored procedure which would execute an INNER JOIN code block based on a parameter value. However, I keep getting "Incorrect syntax near '@reSourceID'." 

if (@VendorID = 11)
    @reSourceID = 't.reSourceID'
if (@VendorID = 5)
    @reSourceID = 't.SourceID'

SELECT      t.ID, fsg.SigCap, fsg.VendorId
FROM        FormCap fsg
    INNER JOIN FlightTrip t
        ON fsg.SourceId = @reSourceID
        AND fsg.VendorId = @VendorID
    INNER JOIN ContractProvider cpu 
        ON t.Id = cpu.VendorId 
WHERE       (t.ID = @FinTransID)
AND     (cpu.userID = @UserID)

Any ideas what would be causing the error?

Upvotes: 0

Views: 7367

Answers (3)

Code Magician
Code Magician

Reputation: 23972

As Bill answered, the optimim solution is probably going to involve dynamic SQL although I'd recommend going a little farther both for performance and security.

If you parameterize your call to sp_executeSQL you'll both avoid issues with escaping quotes and potential SQL injection problems but it will allow SQL to reuse the plan since the query text won't change.

Here's what that would look like:

if (@VendorID = 11)
    @reSourceID = 't.reSourceID'
if (@VendorID = 5)
    @reSourceID = 't.SourceID'

DECLARE @sql NVARCHAR(MAX)
DECLARE @params NVARCHAR(500)    

SET @sql ='SELECT t.ID, 
                  fsg.SigCap, 
                  fsg.VendorId
           FROM FormCap fsg
               INNER JOIN FlightTrip t
                   ON fsg.SourceId = '+CONVERT(NVARCHAR(MAX),@reSourceID)+'
                       AND fsg.VendorId = @VendorID
               INNER JOIN ContractProvider cpu 
                   ON t.Id = cpu.VendorId 
           WHERE t.ID = @FinTransID
               AND       cpu.userID = @userID'

SET @params = N'@VendorID INT, @FinTransID INT, @userID UNIQUEIDENTIFIER' 

EXECUTE sp_executesql @sql, @params, @venderID = @venderID, @finTransID=@finTransID, @userID = @userID

Above is the gist of the solution, I don't fully know your types and inputs. Also, without schema, I can't test my code, but it should get your going.

For more information on sp_executesql, msnd is a great resource.

Upvotes: 2

Anon
Anon

Reputation: 10908

SELECT      t.ID, fsg.SigCap, fsg.VendorId
FROM        FormCap fsg
    INNER JOIN FlightTrip t
        ON fsg.SourceId = CASE @VendorID
                              WHEN 11 THEN t.reSourceID
                              WHEN  5 THEN t.SourceID
                          END
        AND fsg.VendorId = @VendorID
    INNER JOIN ContractProvider cpu 
        ON t.Id = cpu.VendorId 
WHERE       (t.ID = @FinTransID)
AND     (cpu.userID = @UserID)

Upvotes: 2

Bill Gregg
Bill Gregg

Reputation: 7147

Your syntax error is because you haven't really joined on FlightTrip t. You can't do what you are trying to do in compiled SQL. You would have to create a varchar variable full of your statement and then use EXEC SP_EXECUTESQL(@Statement) to run it.

declare @statement nvarchar(4000)

select @statement = '
           SELECT      t.ID, 
                       fsg.SigCap, 
                       fsg.VendorId
           FROM        FormCap fsg
           INNER JOIN  FlightTrip t
           ON          fsg.SourceId = '+convert(nvarchar(100),@reSourceID)+'
           AND         fsg.VendorId = '+convert(nvarchar(100),@VendorID)+'
           INNER JOIN  ContractProvider cpu 
           ON          t.Id = cpu.VendorId 
           WHERE      t.ID = '+convert(nvarchar(100),@FinTransID)+'
           AND       cpu.userID = '''+convert(nvarchar(100),@UserID)+''''

exec sp_executesql @statement

Upvotes: 4

Related Questions