Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: sproc create view and parameter problem

    In the below sproc I want to create a view based on the incoming parameter, which is a date passed on from VB in the format 01-01-2006.

    Yet getting that to work, seems quite a challenge.

    I've been dabbling a bit with the sproc and QA, passing the date parameter to it in varous forms, using both single, double and triple quotationmarks and changing the parameter type to datetime.

    I either wind up with a syntax error " Incorrect syntax near '01' " or " Unclosed quotation mark before the character string ".

    Anyone have a guess at how to fix this issue?

    Cheers, Trinsan

    Code:
    REATE PROCEDURE KONTROL_DRdobbeltrapportering
    
    @rapdato as varchar
    
    AS
    
    if exists (select * from dbo.sysobjects where id = object_id(N'[KONTROL_vw_DRdobbeltrappbasis]') and OBJECTPROPERTY(id, N'IsView') = 1)
    drop view [KONTROL_vw_DRdobbeltrappbasis]
    
    EXEC('
    CREATE VIEW KONTROL_vw_DRdobbeltrappbasis
    AS
    	SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR], 
    	Gramex_DW.dbo.Programlinie.Stationskode, 
    	Gramex_DW.dbo.Programlinie.Udsendelsesdato, 
    	Gramex_DW.dbo.Programlinie.[Udsendelses starttid], 
    	Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode, 
    	Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID, 
    	Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer, 
    	Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode, 
    	Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst, 
    	Gramex_DW.dbo.Programlinie.[Afregnet Station], 
    	Gramex_DW.dbo.Programlinie.[Afregnet den], 
    	Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse], 
    	Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR], 
    	Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID], 
    	Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af], 
    	Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af], 
    	Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn, 
    	Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel, 
    	Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår, 
    	Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer, 
    	Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer], 
    	Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange], 
    	Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer, 
    	Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex], 
    	Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde], 
    	Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer
    	FROM Gramex_DW.dbo.Programlinie 
    	INNER JOIN Gramex_DW.dbo.Programhoved 
    	ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode) 
    	AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato) 
    	AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid])
    	WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ' + @rapdato + '
    	AND (Gramex_DW.dbo.Programlinie.Mediekode <> 1) AND 
                 (Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'') AND 
                 (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'') AND (Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''')
    GO
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Skipping over the "Why?" question....
    You need to enclose the @rapdato value in quotes and you need a closing bracket at the end of the statement although personally I would remove all the brackets from the where and from clauses as they are only obfuscatory IMO.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    There's gotta be a why. From what I gather, the sproc creates a view, used by a vb program to export data.

    I sppose you can alawys wonder why it isn't made directly in the vb proc as a recordset set. But I suppose there's a reason to it, and I can't be bothered figuring out why.

    I'm just the janitor... sweep other peoples mess up :-)

    edit: I just figured I'd post the result and working code... and the reason for doing it seems to be a few other views building their results off the view being create through user input. So there's reason to the madness.

    Code:
    EXEC('
    CREATE VIEW dbo.KONTROL_vw_DRdobbeltrappbasis
    AS
    	SELECT Gramex_DW.dbo.Programhoved.[Rettet af DR], 
    	Gramex_DW.dbo.Programlinie.Stationskode, 
    	Gramex_DW.dbo.Programlinie.Udsendelsesdato, 
    	Gramex_DW.dbo.Programlinie.[Udsendelses starttid], 
    	Gramex_DW.dbo.Programlinie.Rapporteringsdato, Gramex_DW.dbo.Programlinie.Mediekode, 
    	Gramex_DW.dbo.Programlinie.MUSANummer, Replace(Left(convert(varchar, [Gramex-ID]),11),''.'','''') AS GramexID, 
    	Gramex_DW.dbo.Programlinie.Side, Gramex_DW.dbo.Programlinie.Tracknummer, 
    	Gramex_DW.dbo.Programlinie.ISRC, Gramex_DW.dbo.Programlinie.Producentlandekode, 
    	Gramex_DW.dbo.Programlinie.Spilletid, Gramex_DW.dbo.Programlinie.Minuttakst, 
    	Gramex_DW.dbo.Programlinie.[Afregnet Station], 
    	Gramex_DW.dbo.Programlinie.[Afregnet den], 
    	Gramex_DW.dbo.Programlinie.[Afregnings ID], Gramex_DW.dbo.Programlinie.[Ur-opførelse], 
    	Gramex_DW.dbo.Programlinie.Talestof, Gramex_DW.dbo.Programlinie.[Betalt af DR], 
    	Gramex_DW.dbo.Programlinie.Kending, Gramex_DW.dbo.Programlinie.[Oprettelses ID], 
    	Gramex_DW.dbo.Programlinie.[Oprettet den], Gramex_DW.dbo.Programlinie.[Oprettet af], 
    	Gramex_DW.dbo.Programlinie.[Ændret den], Gramex_DW.dbo.Programlinie.[Ændret af], 
    	Gramex_DW.dbo.Programlinie.[Eventuel delskæring], Gramex_DW.dbo.Programlinie.Plademærkenavn, 
    	Gramex_DW.dbo.Programlinie.Katalogbetegnelse, Gramex_DW.dbo.Programlinie.Tracktitel, 
    	Gramex_DW.dbo.Programlinie.Selskabsnummer, Gramex_DW.dbo.Programlinie.Indspilningsår, 
    	Gramex_DW.dbo.Programlinie.Fonogramtitel, Gramex_DW.dbo.Programlinie.Plademærkenummer, 
    	Gramex_DW.dbo.Programlinie.[Antal opførelser], Gramex_DW.dbo.Programlinie.[Rec nummer], 
    	Gramex_DW.dbo.Programlinie.Indslagsnummer, Gramex_DW.dbo.Programlinie.[Afspillet gange], 
    	Gramex_DW.dbo.Programlinie.[Afspillet minutter], Gramex_DW.dbo.Programlinie.Spilletidsløbenummer, 
    	Gramex_DW.dbo.Programlinie.Pointtildelt, Gramex_DW.dbo.Programlinie.[Rettet af Gramex], 
    	Gramex_DW.dbo.Programlinie.[Pgmkorlinie løbenummer], Gramex_DW.dbo.Programlinie.[Tape Kilde], 
    	Gramex_DW.dbo.Programlinie.[Hoved artist], Gramex_DW.dbo.Programhoved.Produktionsnummer
    	FROM Gramex_DW.dbo.Programlinie 
    	INNER JOIN Gramex_DW.dbo.Programhoved 
    	ON (Gramex_DW.dbo.Programlinie.Stationskode = Gramex_DW.dbo.Programhoved.Stationskode) 
    	AND (Gramex_DW.dbo.Programlinie.Udsendelsesdato = Gramex_DW.dbo.Programhoved.Udsendelsesdato) 
    	AND (Gramex_DW.dbo.Programlinie.[Udsendelses starttid] = Gramex_DW.dbo.Programhoved.[Udesendelses starttid])
    	WHERE Gramex_DW.dbo.Programlinie.Rapporteringsdato >= ''' + @rapdato + '''
    	AND Gramex_DW.dbo.Programlinie.Mediekode <> 1 AND 
                 Gramex_DW.dbo.Programlinie.Spilletid >= ''00:01:00'' AND Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2'' AND 
                 Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 ZULU'' AND Gramex_DW.dbo.Programlinie.Stationskode <> ''TV2 CHARLIE''')
    GO
    Last edited by Trinsan; 08-21-06 at 10:28.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •