Meldung 137: Die '@..'-Skalarvariable muss deklariert werden

[BorisHendrik]

Hallo,

STFW und die Suche hier im Forum hat mich leider noch nicht weitergebracht.

Ich habe eine Stored Procedure geschrieben um eine dynamische SQL Abfrage zu bekommen. Dabei habe ich mich an den Artikel von Erland Sommarskog hier gehalten.

Dabei ist diese Procedure herausgekommen:

CREATE PROCEDURE AskForListOfNpis
			@IdStatus	int		= NULL,
			@debug		bit		= 0 AS
Declare @sql		nvarchar(4000),
		@paramlist	nvarchar(4000)
		
SELECT @sql =
	'SELECT t1.IdProject, t2.ProjectType, t3.Status, t1.Name, t1.DateIntendedSignOff, t1.DateEnd,
              v1.ApprPo, v1.ApprProd, v1.ApprIrc, v1.ApprCaa, v1.ApprFm, v1.ApprDo
        FROM dbo.tblMain AS t1 
        INNER JOIN dbo.tblProjectType AS t2 ON t1.IdProjectType = t2.IdProjectType 
        INNER JOIN dbo.tblStatus AS t3 ON t1.IdStatus = t3.IdStatus
        INNER JOIN dbo.viewApproval AS v1 ON t1.IdProject = v1.IdProject
        WHERE 1 = 1'

If @idStatus IS NOT NULL
   SELECT @sql = @sql + ' AND t1.IdStatus = @IdStatus'
   
SELECT @sql = @sql + ' ORDER BY t1.IdProject'

IF @debug = 1
   Print @sql
   
SELECT @paramlist = '@IdStatus	int'

EXEC sp_executesql @sql, @paramlist, @IdStatus

Wenn ich die Procedure mit:

EXECUTE AskForListOfNpis

aufrufe, bekomme ich alle Datensätze zurück. Nur der Aufruf mit dem Parameter geling nicht. Ich habe es so:

EXECUTE AskForListOfNpis  '',1

mit diesem Ergebnis:

SELECT t1.IdProject, t2.ProjectType, t3.Status, t1.Name, t1.DateIntendedSignOff, t1.DateEnd,
              v1.ApprPo, v1.ApprProd, v1.ApprIrc, v1.ApprCaa, v1.ApprFm, v1.ApprDo
        FROM dbo.tblMain AS t1 
        INNER JOIN dbo.tblProjectType AS t2 ON t1.IdProjectType = t2.IdProjectType 
        INNER JOIN dbo.tblStatus AS t3 ON t1.IdStatus = t3.IdStatus
        INNER JOIN dbo.viewApproval AS v1 ON t1.IdProject = v1.IdProject
        WHERE 1 = 1 AND t1.IdStatus = @IdStatus ORDER BY t1.IdProject
Meldung 137, Ebene 15, Status 2, Zeile 7
Die '@IdStatus'-Skalarvariable muss deklariert werden.

Alle Kombinationen aus Kommas, Anführungszeichen und dem Parameter führen zu keinem Ergebnis.

Was mache ich falsch? Wie muss der korrekte Aufruf lauten?

Besten Dank

BorisHendrik

[O. Helper]

Hallo BorisHendrik,

es liegt an dieser Zeile:[highlight=SQL]SELECT @sql = @sql + ' AND t1.IdStatus = @IdStatus'[/highlight]
das muss
:[highlight=SQL]SELECT @sql = @sql + ' AND t1.IdStatus = ' + convert(varchar(10), @IdStatus)[/highlight]
heissen; das dynamische SQL kann nicht auf die Variablen innerhalb der SP zugreifen, also musst Du sie beim zusammenstellen bereits auflösen.

[O. Helper]

Bei genauerer Betrachtung ist die ursprünglich SP doch ok (my fault); @IdStatus wird ja per Parameter übergeben.

Allerdings solltest Du für den Int-Wert keinen Leerstring übergeben.
Was kommt raus, wenn Du es so aufrufst:

EXECUTE AskForListOfNpis 0, 1

[ebis]

Bei genauerer Betrachtung ist die ursprünglich SP doch ok (my fault); @IdStatus wird ja per Parameter übergeben.

Allerdings solltest Du für den Int-Wert keinen Leerstring übergeben.
Was kommt raus, wenn Du es so aufrufst:

EXECUTE AskForListOfNpis 0, 1

Das wirds auch nicht sein, denn

SELECT CONVERT (INT, '') ergibt ja 0 ......

ob nun explizit konvertiert wie hier oder implizit...

Ich denke, die letzte Zeile muss anstatt

EXEC sp_executesql @sql, @paramlist, @IdStatus

EXEC sp_executesql @sql, @paramlist, @IdStatus=@IdStatus

lauten.

[O. Helper]

Ok, am Leerstring liegt es nicht. Wenn ich die SP zu zusammenkürze, das nicht auf Tabellen selektiert wird:
[highlight=SQL]USE [AdventureWorks]
GO
/****** Objekt: StoredProcedure [dbo].[AskForListOfNpis] Skriptdatum: 10/08/2009 10:04:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AskForListOfNpis]
@IdStatus int = NULL,
@debug bit = 0 AS
Declare @sql nvarchar(4000),
@paramlist nvarchar(4000)

SELECT @sql = 'SELECT 1 '

If @idStatus IS NOT NULL
SELECT @sql = @sql + ', @IdStatus'

IF @debug = 1
Print @sql

SELECT @paramlist = '@IdStatus int'

EXEC sp_executesql @sql, @paramlist, @IdStatus

GO
exec [AskForListOfNpis] NULL,1
exec [AskForListOfNpis] 1,1[/highlight]
funktioniert es, ohne Fehlermeldung.

[BorisHendrik]

Hallo,

beides führt leider zu keiner Änderung. Ich habe zunächst versucht die Prozedur mit

EXECUTE AskForListOfNpis 0, 1

aufzurufen. Ergebnis:

SELECT t1.IdProject, t2.ProjectType, t3.Status, t1.Name, t1.DateIntendedSignOff, t1.DateEnd,
              v1.ApprPo, v1.ApprProd, v1.ApprIrc, v1.ApprCaa, v1.ApprFm, v1.ApprDo
        FROM dbo.tblMain AS t1 
        INNER JOIN dbo.tblProjectType AS t2 ON t1.IdProjectType = t2.IdProjectType 
        INNER JOIN dbo.tblStatus AS t3 ON t1.IdStatus = t3.IdStatus
        INNER JOIN dbo.viewApproval AS v1 ON t1.IdProject = v1.IdProject
        WHERE 1 = 1 AND t1.IdStatus = @IdStatus ORDER BY t1.IdProject
Meldung 137, Ebene 15, Status 2, Zeile 7
Die '@IdStatus'-Skalarvariable muss deklariert werden.

Dann habe ich die SP in der letzten Zeile auf
... @IdStatus=@IdStatus
geändert.

Wenn ich nun
EXECUTE AskForListOfNpis
ohne Parameter ausführe bekomme ich die Fehlemeldung:

Meldung 8178, Ebene 16, Status 1, Zeile 0
Die parametrisierte Abfrage '(@xIdStatus int)SELECT t1.IdProject, t2.ProjectType, t3.Status, ' erwartet den '@xIdStatus-Parameter, der nicht bereitgestellt wurde.

???

[O. Helper]

Sekunde mal: Führst Du nur die SP aus oder verwendest Du das SQL Statement, das im Debug Mode ausgegeben wird, und willst Du das ausführen, was zum Fehler führt?

[BorisHendrik]

Wenn ich die geänderte Prozedur mit

EXECUTE AskForListOfNpis '', 1

oder

EXECUTE AskForListOfNpis 0, 1

ausführe, kommt wieder die oben gezeigte Meldung 137 mit geprintetem SQLString.

EXECUTE AskForListOfNpis 1

gibt nur die Meldung 137 zurück, ohne den SQL String

DECLARE @IdStatus Int
SET @IdStatus = 1
EXECUTE AskForListOfNpis @IdStatus

gibt nur die Meldung 137 zurück, ohne den SQL String

[BorisHendrik]

@O.Helper

Ich habe unter Programmierbarkeit/Gespeicherte Prozeduren/Gespeicherte Systemprozeduren einen Eintrag dbo.AskForListOfNpis.

Dann habe ich einen rechts klick auf diesen Eintrag gemacht, "gespeicherte Prozedur ausführen" gewählt und in dem dann geöffneten Fenster "Prozedur ausführen" für den Parameter @IdStatus den Wert 1 eingegeben.

Wenn ich dann auf OK klicke bekomme ich einen Tab mit der Query
[Code]
USE [NPI_Tool_DB1]
DECLARE @return_value = [dbo].[AskForListOfNpis]
SELECT #Return Value = @return_value
GO

Ergebnis:

Meldung 8178, Ebene 16, Status 1, Zeile 0
Die parametrisierte Abfrage '(@xIdStatus int)SELECT t1.IdProject, t2.ProjectType, t3.Status, ' erwartet den '@xIdStatus-Parameter, der nicht bereitgestellt wurde.

In diesem Fenster habe ich dann alles gelöscht und die oben beschriebenen Versuche durchgeführt.

EXCECUTE Ask..

hat zumindest mal alles zurückgegeben.

[O. Helper]

Wo kommt den nun wieder die Variable @xIdStatus weg, hast Du da zwischenzeitlich was umbenannt; im urspr. Script ist sie nicht drin?

[BorisHendrik]

Hallo Olaf,

da hast Du recht, äähh, hätte aber drin sein sollen, in dem Beispiel von Erland Sommarskog war es so, um zu zeigen, das die Variablen in und ausserhalb der Prozedur in keinem Zusammenhang stehen.

Wie auch immer, ... es geht!! Isch werd verrückt, es geht!!!

Was habe ich denn jetzt gemacht?

Ein Rechtsklick auf meine gespeicherte Prozedur "Ändern" erstellt eine neue Query mit diesem Inhalt:

USE [NPI_Tool_DB1]
Go
/***** ...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OM
GO
-- Batch ....
-- Batch ....
ALTER PROCEDURE [dbo].[AskForListOfNpis]
@IdStatus int = NULL,
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
	@paramlist nvarchar(4000)
SELECT @sql =
	'SELECT t1.IdProject, t2.ProjectType, t3.Status, t1.Name, t1.DateIntendedSignOff, t1.DateEnd,
              v1.ApprPo, v1.ApprProd, v1.ApprIrc, v1.ApprCaa, v1.ApprFm, v1.ApprDo
        FROM dbo.tblMain AS t1 
        INNER JOIN dbo.tblProjectType AS t2 ON t1.IdProjectType = t2.IdProjectType 
        INNER JOIN dbo.tblStatus AS t3 ON t1.IdStatus = t3.IdStatus
        INNER JOIN dbo.viewApproval AS v1 ON t1.IdProject = v1.IdProject
        WHERE 1 = 1'

If @idStatus IS NOT NULL
   SELECT @sql = @sql + ' AND t1.IdStatus = @IdStatus'
   
SELECT @sql = @sql + ' ORDER BY t1.IdProject'

IF @debug = 1
   Print @sql
   
SELECT @paramlist = '@IdStatus int'

EXEC sp_executesql @sql, @paramlist, @IdStatus

Wenn ich nun eine neue Query mache mit diesem Inhalt:

EXECUTE AskForListOfNpis 1,1

geht es.

Wo ist denn da jetzt der Unterschied?

mmhhh, ich sehe, es ist noch ein weiter Weg bis ich das wirklich verstehe. Danke für Eure Hilfe.

Beste Grüsse
Boris

[ebis]

was fördert ein

SELECT * FROM sys.Objects WHERE Name = 'AskForListOfNpis'

zu Tage?

Immerhin ist bei der allersersten Definition der SP kein Schema angegeben...

[BorisHendrik]

Das:

name: AskForListOfNpis
object_id: 1234103437
principal_id: NULL
schema_id: 1
parent_object_id: 0
type: P
type_desc: SQL_STORED_PROCEDURE
create_date: ...
modify_date: ....
is_ms_shipped: 0
is_published: 0
is_schema_published: 0