Geek Answers Handbook

Field extraction scheme in Visual Studio stored procedure that uses temporary tables

I have several complex stored procedures that pull data from other databases using linked servers. This data is placed in temporary tables, which are combined into a selected query for output. Procedures work fine, but in visual studio, if I try to add a stored procedure to a dataset using a constructor, I get an invalid error name #tmp or whatever the first temp table calls. It cannot get the database schema. This is the same for use and sqldatasource in ASP.NET.

This procedure is still in use, but I need to manually add all the columns that it should output to datatable. It will be painful to do it manually, and I believe that this is due to the way the visual studio collects the output fields from the stored procedure, it does not seem to start it in the usual way. Is there a way to fix this, since I have a lot of them, and you don’t want to add all the columns manually, which is time consuming and error prone.

+5
source share
4 answers

After searching for a while, I found a fancy solution:

IF 1=0 BEGIN
SET FMTONLY OFF
END

. , , , . FMTONLY . SQL Server 2005.

+12

:

FMTONLY OFF

, - IF 1 = 0 ? , , , FMTONLY , IF/ELSE !

FMTONLY , SSRS Visual Studio, stp. FMTONLY - . , , , , , temp, .

FMTONLY ON stp . - // stp, , FMTONLY, , .

, FMTONLY, 1 = 0. , FMTONLY , , stp.

+2

, , , #Temp temp. , TOP 0

SELECT TOP 0
    CONVERT (NULL, VarChar (30)) AS Column1,
    CONVERT (NULL, INTEGER) AS Column2,
    CONVERT (NULL, DECIMAL (9, 2)) AS Column3,
    CONVERT (NULL, VarChar (55)) AS Column4

UNION

SELECT *
FROM #MyTempTable
0

, , , . .

If Exists(Select 1 From Table Where Column1 = 7)
  Select Col1, Col2 From Table2
Else
  Select Col3, Col4, Col5 From Table2

I would never write a stored procedure that has a different number of columns depending on the data, but it is possible. For a moment, suppose you had a stored procedure like this. Which column list should be returned?

0
source

More articles:


All Articles