none
Variable wird ignoriert RRS feed

 > 

  • Frage

  • Question
    Anmelden
    0
    Anmelden
    Hallo

    ich möchte gerne eine gespeicherte Prozedur erstellen, wo ich einige Parameter schon in der Prozedur festlegen möchte, z.B. die Sortierung, die sich zur Laufzeit auch ändern kann.

    Hier mal der Scriptcode. Variable @sort1 ist der Übeltäter. Der soll das order by im over() zur Laufzeit veränden, tut er aber nicht. 

    ALTER PROCEDURE [dbo].[sp_seminarPaging]
    @Page INT,
    @PageSize INT,
    @sort1 varchar(100) = 'beginn ASC',
    @kat varchar(50)= '%'       
       
AS
BEGIN   
SET NOCOUNT ON;
   
    WITH rsContacts AS
    (
   
        SELECT
            ROW_NUMBER() OVER(ORDER BY @sort1) AS 'RowNr',
            kursid, Beginn, themen, planung, kat
        FROM seminar
        WHERE Beginn > GETDATE() and planung = 0 and Kat like @kat
       
    )

    SELECT *
    FROM rsContacts
    WHERE RowNr BETWEEN (@Page - 1) * @PageSize + 1
        AND @Page * @PageSize

END


    Hier die Abfrage
    EXEC sp_seminarPaging @Page = 1, @PageSize = 10,@sort1='themen ASC'
    Es kommt kein Fehler, aber es wird auch nicht sortiet, weder nach dem Default-Wert noch nach dem Wert, den ich ihr bei der Ausgabe zugewiesen habe.

    Was mache ich falsch?

    Über eine Antwort würde ich mich sehr freuen.

    Liebe Grüße, die Luzie!
    Sonntag, 25. Oktober 2009 20:24
    |

Alle Antworten

  • Question
    Anmelden
    1
    Anmelden
    Es kommt deswegen kein Fehler, weil es ja auch kein Fehler ist. @sort1 wird als Zeichenfolge erkannt und interpretiert, nicht als der dahinterliegende Spaltenname. Du sortierst also zur Laufzeit nach einem konstanten Wert und deswegen siehst Du kein "korrekt" sortiertes Ergebnis.
    Schau Dir das mal an:
    http://www.insidesql.org/beitraege/entwicklung/dynamisches-sql-fluch-und-segen
    http://www.insidesql.org/beitraege/entwicklung/dynamisches-order-by-kriterium
    -- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org
    Montag, 26. Oktober 2009 11:40
    |
  • Question
    Anmelden
    0
    Anmelden
    Hallo

    vielen Dank für die interessanten Links.

    Ich habe es jetzt erstmal wie folgt gelöst. 

    ALTER PROCEDURE [dbo].[sp_seminarPaging]
    @Page INT,
    @PageSize INT,
    @sort1 bit = 1,
    @kat varchar(50)= '%'

       
AS
BEGIN   
SET NOCOUNT ON;
   
    WITH rsContacts AS
    (
   
        SELECT
            ROW_NUMBER() OVER(ORDER BY
            case when @sort1=1 then beginn end,
            case when @sort1=2 then kursid end,
            case when @sort1=3 then detail end
            ASC) AS 'RowNr',
            kursid, Beginn, themen, planung, kat, detail
        FROM seminar
        WHERE Beginn > GETDATE() and planung = 0 and Kat like @kat
       
    )

    SELECT *
    FROM rsContacts
    WHERE RowNr BETWEEN (@Page - 1) * @PageSize + 1
        AND @Page * @PageSize
           
END
    Als nächstes muss ich mich jetzt an den dyn. Aufbau der Where-Bedingung machen. Solange es um eine Spalte vom Typ varchar geht (kat), kann ich mich ja noch mit einem Wildcard behelfen. Aber da gibt es ja noch Themengebiet und Kurs-Nrn., die vom Type INT sind und Beginn und Ende vom Typ Date.

    Ist es auch möglich, dass die Where-Bedingung separat in einer Variablen declariert, abgefragt und ausgeführt wird um sie anschließend an die SQL Select-Anweisung anzuhängen?

    z.B. 
    DECLARE @where nvarchar(4000)

   SET @where = 'SET QUOTED_IDENTIFIER OFF
                 WHERE themen like "%' + @themen + '%"'
   EXEC(@where)
    so in der Art.

    Ich habe nur Beispiele gefunden (wie auch in Deinem Link), wo das komplette select-Statement declariert und ausgeführt wird.
    Liebe Grüße, die Luzie!
    Montag, 26. Oktober 2009 13:10
    |
  • Question
    Anmelden
    0
    Anmelden
    Hallo

    ich brauche nochmals dringend eure Hilfe. Ich sitze jetzt seit 2 Tagen an der Erstellung einer dyn. SQL in einer Stored procedure und komme einfach nicht weiter.

    Ich brauche zum einen die Row-Number und die entsprechende Bedingung und zum anderen eine dyn. aufgebaute Where-Bedingung. Ich habe 2 unterschiedliche Prozeduren erstellt und muss diese nun zu einer zusammenbringen. Es scheitert daran, die dyn. Abfrage in die temporäre Select-Abfrage (WITH rsContacts AS) einzubauen.

    Weiterhin brauche ich die dyn. Zusammenstellung der Parameter in der Prozedur sp_KritToWhere. Sind die Parameter mit 0 ausgeführt, dürfen sie im SQL-Statement nicht auftauchen. In der Prozedur funktioniert die Abfrage.

    Aber diese Abfrage MUSS nun in die Prozedur sp_seminarPaging integriert werden. Aber ich weiß echt nicht wie?

    Ich bin leider noch nicht so routiniert in Bezug auf T-SQL, deshalb bitte ich an dieser Stelle um Hilfe. Ich brauch nur einen kleinen Tipp.  

    Vielen Dank.

    USE [ptsService]
GO
/****** Object:  StoredProcedure [dbo].[sp_KritToWhere]    Script Date: 10/29/2009 09:23:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_KritToWhere]
(
     @NEU bit,
     @BS bit,
     @CH bit
)

AS

DECLARE @sql varchar(8000)
DECLARE @hasWhere bit

SET @sql = 'SELECT kursid, NEU, BS, CH, Themen         
            FROM Seminar
            where planung=0 and beginn >=Getdate()'

SET @hasWhere = 0

IF @NEU <> 0
BEGIN
   SET @sql = @sql + ' and (NEU=1'
   SET @hasWhere = 1
END

IF @BS <> 0
BEGIN
   IF @hasWhere = 0
        BEGIN
            SET @sql = @sql + ' and (BS=1'
            SET @hasWhere = 1
        end
   ELSE
        SET @sql = @sql + ' or BS=1'                       
END
IF @CH <> 0
BEGIN
   IF @hasWhere = 0
        BEGIN
           SET @sql = @sql + ' and (CH=1'
           SET @hasWhere = 1
        END                
    ELSE
      SET @sql = @sql + ' or CH=1'                        
END

IF @hasWhere = 1
    SET @sql = @sql + ')'
    
SET @sql = @sql + ' ORDER BY beginn'
print @sql
print @haswhere
EXEC(@sql)



    USE [ptsService]
GO
/****** Object:  StoredProcedure [dbo].[sp_seminarPaging]    Script Date: 10/29/2009 09:45:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_seminarPaging]
    @Page INT,
    @PageSize INT,
    @sort1 bit = 1,
    @detail varchar(20) = '%',
    @kat varchar(50)= '%',
    @suche varchar(50) = '%'
    
        
AS
BEGIN    

SET NOCOUNT ON;
    
    WITH rsContacts AS
    (
    
        SELECT
            ROW_NUMBER() OVER(ORDER BY
            case when @sort1=1 then beginn end,
            case when @sort1=2 then kursid end,
            case when @sort1=3 then detail end
            ASC) AS 'RowNr',
            kursid, CONVERT(char(10), Beginn, 104) AS Datum, themen, NEU, kat, detail
        FROM seminar
        where Beginn > GETDATE() and planung = 0 and Kat like @kat
                and detail like @detail and
                (Themen like '%'+@suche+'%')
        
    )
 
    
    SELECT *
    FROM rsContacts
    WHERE RowNr BETWEEN (@Page - 1) * @PageSize + 1
        AND @Page * @PageSize
        
END


    Liebe Grüße, die Luzie!
    Donnerstag, 29. Oktober 2009 09:22
    |
  • Question
    Anmelden
    0
    Anmelden
    So, ich habs hinbekommen.
    Danke nochmals für die Links, die haben mir sehr weitergeholfen.

    Einträge aus unterschiedlichen Foren und Blogs haben mir auch weitergeholfen, die dyn. SQL in T-SQL zu verstehen. Mein Problem war vor allem, eine dyn. Abfrage mehrerer Parameter innerhalb einer Klammer mit or-Operator auszuführen. Hier habe ich mit dem Parameter @haswhere gearbeitet, welcher steuert, ob der ausgeführte Parameter am Anfang oder in der Mitte bzw. am Ende steht. Dies ist bestimmt nur eine Möglichkeit, die Prozedur zu erstellen, mit Sicherheit gibt es noch andere, vielleicht auch bessere. :)

    Alter PROCEDURE sp_testSuche                              
          @page int = 1,
          @pagesize int = 10,
          @kat varchar(10) = '%',
          @detail varchar(20) = '%',
          @NEU bit = NULL,
          @BS bit = NULL,
          @CH bit = NULL,
          @sort varchar(20) = 'beginn ASC',
          @debug bit = 0
 
AS                 
                                                   
DECLARE @sql nvarchar(4000)   
DECLARE @paramlist nvarchar(1000)
DECLARE @haswhere bit
 
SET @sql = 'WITH rsContacts as
(
   SELECT ROW_NUMBER() OVER(ORDER BY ' + quotename(@sort) +') AS [RowNr],
   kursid, CONVERT(char(10), Beginn, 104) AS Datum, themen, NEU,BS,CH,kat, detail
   FROM seminar    
   WHERE  beginn >=getdate()
        and kat like '+ quotename(@kat, '''') +'
        and detail like '+ quotename(@detail, '''') +'
        and planung=0'
  
   SET @hasWhere = 0

    IF @NEU <> 0
    BEGIN
       SET @sql = @sql + ' and (NEU=1'
       SET @hasWhere = 1
    END

    IF @BS <> 0
    BEGIN
       IF @hasWhere = 0
            BEGIN
                SET @sql = @sql + ' and (BS=1'
                SET @hasWhere = 1
            end
       ELSE
            SET @sql = @sql + ' or BS=1'                      
    END
    IF @CH <> 0
    BEGIN
       IF @hasWhere = 0
            BEGIN
               SET @sql = @sql + ' and (CH=1'
               SET @hasWhere = 1
            END               
        ELSE
          SET @sql = @sql + ' or CH=1'                       
    END

    IF @hasWhere = 1
    Begin
        SET @sql = @sql + ')'
    END   

SET @sql = @sql + ')
                                                 
 SELECT *
    FROM rsContacts
    WHERE RowNr BETWEEN (@Page - 1) * @PageSize + 1
    AND @Page * @PageSize' 
                                                          
SET @paramList = '@page int,
                    @pagesize int,
                    @kat varchar(10),
                    @detail varchar(20),
                    @NEU bit,
                    @BS bit,
                    @CH bit,
                    @sort varchar(20)'                                          


IF @debug = 1 PRINT @sql
       
EXEC sp_executesql
        @sql,
        @paramlist,
        @page,
        @pagesize,
        @kat,
        @detail,
        @NEU,
        @BS,
        @CH,
        @sort
    Liebe Grüße, die Luzie!
    Freitag, 30. Oktober 2009 21:22
    |