• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1166
  • Last Modified:

How do I run Firebird SQL Select Statements from a MS SQL Server Stored Procedure

Hi.  I am writing a stored procedure in SQL Server that I plan to use to execute some customer-provided Firebird SQL Select Statements.  I have not seen these Firebird SQL Statements, and I assume for purposes of this question that what they do is irrelevant.  If this assumption is incorrect, please advise and I will attempt to spell out the purpose of the Firebird SQL Statements.

What I am trying to do is "wrap" these Firebird SQL Statements inside my stored procedure, which will be executed by MS SQL Server 2005.  I have an ODBC connection to the Firebird database, and I have created a linked server environment in MS SQL Server that successful connects to the Firebird database.  Can someone show me how to call or use those Firebird statements within my stored procedure?  Thanks.

A high level example:

if @IsLookup = 1
     BEGIN
        SET @DataToFirebird = @AccountNumber
        --At this point I want to know how to call the Firebird scripts
        --Now, I will use returned data from the Firebird scripts in my stored procedure
      END

Again, thanks, and please let me know what information is needed; I am sure I have left out pertinent information, but I am not familiar at all with Firebird, and only some experience with SQL Server.
0
jhstevens
Asked:
jhstevens
  • 2
1 Solution
 
mcv22Commented:
0
 
pbarry1Commented:
Hi,

there's an easy way to do it.  Just use the "EXEC ('Firebird SQL Statement') AT YourFirebirdLinkedServerName" syntax

Here's a sample:

--Create a temporary table
CREATE TABLE #test (c1 VARCHAR(255))

-- Insert the result in the temporary table #test.  Note: in this sample, the Firebird statement returns only a column, but you can return more if you want.  Just adjust the #test definition and the insert)
INSERT INTO #test (c1)
  EXEC ('Your Firebird SQL statement') AT YourFirebirdLinkedServer
 
-- Display the results
SELECT * FROM #test

-- Delete the temporary table
DROP TABLE #test
0
 
jhstevensAuthor Commented:
I wanted to dig a bit deeper here.  I have tested the accepted solution and it works perfectly.  What I would like to know is if it is possible to set the return value from "EXEC ('Your Firebird SQL statement') AT YourFirebirdLinkedServer" into a variable as opposed to a temp table.  The reason is that the stored procedure may be called along multiple threads, and this will (I believe) cause a problem with the temp tables (creating an existing table, dropping, etc).  So, is is possible to insert this return value into a variable?  Thanks.

Heath
0
 
pbarry1Commented:
Hi, here's how to do it with a variable:

-- Declare a variable of type TABLE. Will contain your firebird resultset containing the return value
DECLARE @vResult TABLE (ReturnValue INT)
-- Declare a variable of type INT.  Will contain your return code (assuming that the firebird resultset contains only a return code value)
DECLARE @vReturnCode int

-- Insert the FirebirdResultset into the TABLE variable
INSERT INTO @vResult (ReturnValue)
  EXEC ('Your Firebird SQL Statement') AT YourFirebirdLinkedServer
 
-- Put return value in a variable that is easy to handle as a real return code
SELECT TOP 1 @vReturnCode = ReturnValue FROM @vResult

-- Display the return code
PRINT STR(@vReturnCode)

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now