[SQL] Stored Procedure met dynamische tabel doet het niet

[creator1988]

Bonjour, ik ben voor het eerst in mijn leven bezig met stored procedures, en tot nu toe gaat het goed; alleen ik kom niet uit de volgende.

Ik heb een tabel en die wil ik dynamisch oproepen, dus met wat hulp van google heb ik het volgende in elkaar geknutseld:

code:

USE [BackD]
GO
/****** Object:  StoredProcedure [dbo].[GetComplexRelationField]    Script Date: 03/12/2008 08:24:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetComplexRelationField] 
    @lookupField varchar(100),
    @lookupTable varchar(100),
    @relationField varchar(100),
    @relationID int
AS

    declare @abc varchar(300)

    select @abc = ('SELECT ' + @lookupField + ' AS return_value 
    FROM [dbo].[' + @lookupTable + ']
    WHERE ID = 
        (SELECT ' + @relationField + ' FROM [dbo].[Relation] WHERE
        ID = ' + @relationID + ')')

    exec(@abc)
GO

Dat resulteert in deze fout:

code:

Msg 245, Level 16, State 1, Procedure GetComplexRelationField, Line 15
Conversion failed when converting the varchar value 'SELECT Street AS return_value 
    FROM [dbo].[Address]
    WHERE ID = 
        (SELECT VisitAddressId FROM [dbo].[Relation] WHERE
        ID = ' to data type int.

Dan pas ik het type van @relationID aan naar varchar(100) en dan werkt het wel. Dat vind ik nogal apart, iemand enig idee waarom?


Ik geef de volgende waardes vervolgens mee aan de stored procedure:

@lookupField = Street
@lookupTable = Address
@relationField = VisitAddressId
@relationID = 9

en dan klik ik op execute en krijg ik 2 grids terug in SQL Server Management Studio:


Nou die bovenste wil ik wél, maar die onderste dus mooi niet.
Als ik nu in Visual Studio de Stored Procedure toevoeg aan mijn dataset krijg ik het volgende te zien:


Nou is dat dus niet de bedoeling, want ik wil wel mijn return_value terug krijgen

Als ik gewoon een query inklop (dus zonder dat ik daarna een exec in de procedure doe) dan werkt het allemaal prima, dus daar zit volgens mij de fout. Moet ik daar extra parameters aan meegeven o.i.d.?

[whoami]

Je @relationId bevat een integer, en je wil de inhoud van die integer in je query (varchar / string) plakken.
Je zal dus je @relationId moeten casten / converten naar een varchar, zodanig dat je deze in je string kunt plakken.
Je zou echter ook je query als volgt kunnen opbouwen

code:

SELECT abc = 'SELECT ' + @lookupField + ' AS return_value ' +
                      ' FROM [dbo].[' + @lookupTable + '] '
                     ' WHERE ID = 
        (SELECT ' + @relationField + ' FROM [dbo].[Relation] WHERE
        ID = @relationID )'

Maw, je relationId kan je ook echt als parameter gaan aanschouwen in je query (voor je table and columns gaat dat niet werken). Je zal dan echter in je sp_executesql ook wel die parameters moeten doorgeven.

Het onderste resultaat dat je krijgt, is de return value van een stored procedure (waarmee bv kan aangegeven worden of je SP goed uitgevoerd is of niet)

[ Voor 4% gewijzigd door whoami op 12-03-2008 09:32 ]

[RobIII]

Waar ik me meer zorgen om maak: Wat is je doel van deze Stored Procedure? Want dit riekt naar iets 'vies'

[creator1988]

whoami schreef op woensdag 12 maart 2008 @ 09:30:
Het onderste resultaat dat je krijgt, is de return value van een stored procedure.

Ok. Got it, maar hoe kan ik ervoor zorgen dat mijn resultaatveld het resultaat van de stored procedure wordt, want nu kan ik de waarde niet in VS gebruiken.

[whoami]

Een SP kan een return waarde hebben waarmee je een status returned, maar het is niet de bedoeling dat je SP ook een 'recordset' returned.

Dat je geen resultaat in VS.NET krijgt, heeft ws te maken met het feit dat je die wizard nog niet volledig hebt doorlopen, en je de waardes van de parameters niet gespecifieerd hebt ?

[creator1988]

Maar als ik de volgende SP heb:

code:

USE [BackDoor]
GO
/****** Object:  StoredProcedure [dbo].[GetRelationField]    Script Date: 03/12/2008 09:42:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetRelationField]
    @relationID int,
    @desiredField varchar(100)
AS
    SELECT @desiredField AS return_value FROM [dbo].[Relation] WHERE
    ID = @relationID

Dan krijg ik gewoon netjes dit te zien in VS:


Dus daar doet hij het wel goed. Dat wil ik ook in deze SP;(

[ Voor 5% gewijzigd door creator1988 op 12-03-2008 09:41 ]

[creator1988]

RobIII schreef op woensdag 12 maart 2008 @ 09:31:
Waar ik me meer zorgen om maak: Wat is je doel van deze Stored Procedure? Want dit riekt naar iets 'vies'

Nou er zijn variabelen die een user in een document kan geven ala #relatietype_veldnaam#; maar veldnaam kan ook verwijzen naar een andere tabel dan relation, dus bijvoorbeeld Relation->Address, en om dat in 1 query te kunnen krijgen (die compatible is met alle tabellen), en dat te kunnen gebruiken in 1 koppeltabel hebben we besloten het zo te doen. Alleen is m'n collega die hier wél het fijne van weet vandaag niet aanwezig

[whoami]

Aangezien je je query dynamisch opbouwt in je SP, zal VS.NET niet kunnen weten hoe de resultset van je SP er uit ziet. (Wat logisch is).

[creator1988]

whoami schreef op woensdag 12 maart 2008 @ 09:57:
Aangezien je je query dynamisch opbouwt in je SP, zal VS.NET niet kunnen weten hoe de resultset van je SP er uit ziet. (Wat logisch is).

Maar, kan ik die waarde dan wel ergens vandaan halen?

code:

object res = queriesTA.GetComplexRelationField(relationType.LookupField, relationType.LookupTable, relationType.RelationField, System.Convert.ToString(welkeRelatie.ID));

zorgt namelijk voor een fout.

[whoami]

wat bedoel je ?
Welke fout, waar wil je die waardes vandaan halen ?

Waarom vind je het belangrijk dat je die resultset at design time in vs kunt zien ?

[creator1988]

whoami schreef op woensdag 12 maart 2008 @ 10:05:
wat bedoel je ?
Welke fout, waar wil je die waardes vandaan halen ?

Waarom vind je het belangrijk dat je die resultset at design time in vs kunt zien ?

De service crasht gewoon als ik de bovenstaande functie uitvoer, komt zelfs geen melding omhoog. Blijkbaar wil mijn dataset resultwaardes zien voordat ik een resultaat van die functie kan krijgen?

Ik wordt er in ieder geval gek van, ik grijp voor deze ene keer wel even terug op de SqlDataReader. Thanks anyways.