Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 15th, 2004, 08:22 AM
Registered User
 
Join Date: Sep 2004
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default error building dynamic sql in stored procedure

Welcome Everuone,
I have the following problem: I need a report/view based on the user input what columns he wants to be displayed. Because the only solution seems the dynamic select statement build according to his wishes, so I put this code into the procedure:
CREATE PROCEDURE p_prace_promotora_SHOW_dyna
    @id_pracownika INTEGER = NULL,
    @czy_autorzy BIT = 0,
    @czy_slowa_kluczowe BIT = 0,
    @czy_recenzenci BIT = 0,
    @znaleziono INTEGER OUTPUT
AS
DECLARE @zdanie VARCHAR( 8000), @pozycja INTEGER

SET @pozycja = 0
SET @zdanie = 'SELECT PD.tytul AS [Tytuł], pd.rodzaj AS [Rodzaj], pd.czy_cd AS [Jest CD], pd.data_obrony AS [Obroniona], pd.ocena AS [Ocena],' +
 ' p.nazwisko + p.imie + p.tytul AS [Promotor]'
IF @czy_autorzy
    SET @zdanie = @zdanie + ', s.nazwisko + s.imie AS [Autor]'
IF ( @czy_slowa_kluczowe)
    SELECT @zdanie = @zdanie + ', sl.slowo_kluczowe AS [SÅ‚owo kluczowe]'
IF ( @czy_recenzenci)
    SELECT @zdanie = @zdanie + ', pr.nazwisko + pr.imie + pr.tytul AS [Recenzent]'
SELECT @zdanie = @zdanie + 'FROM praca_dyplomowa AS pd INNER JOIN pracownik AS p ON p.id_pracownika = pd.id_pracownika'
IF ( @czy_autorzy )
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN autorzy AS A ON a.id_pracy = pd.id_pracy'
    SELECT @zdanie = @zdanie + ' INNER JOIN student AS s ON s.id_studenta = a.id_studenta'
END
IF ( @czy_recenzenci)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN recenzenci AS R ON r.id_pracy = pd.id_pracy INNER JOIN pracownik AS PR '
    SELECT @zdanie = @zdanie + 'ON pr.id_pracownika = r.id_pracownika'
END
IF ( @czy_slowa_kluczowe)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN skorowidz AS sk ON sk.id_pracy = pd.id_pracy INNER JOIN slowa_kluczowe AS sl'
    SELECT @zdanie = @zdanie + ' ON sl.id_klucza = sk.id_klucza'
END
SELECT @zdanie = @zdanie + ' WHERE ( pd.id_pracownika = @id_pracownika) AND ( p.id_pracownika = pd.id_pracownika)'
IF ( @czy_autorzy )
    SELECT @zdanie = @zdanie + ' AND ( a.id_pracy = pd.id_pracy) AND ( s.id_studenta = a.id_studenta)'
IF ( @czy_recenzenci)
    SELECT @zdanie = @zdanie + ' AND ( r.id_pracy = pd.id_pracy) AND ( pr.id_pracownika = r.id_pracownika)'
IF ( @czy_slowa_kluczowe)
    SELECT @zdanie = @zdanie + ' AND ( sk.id_pracy = pd.id_pracy) AND ( sl.id_klucza = sk.id_klucza)'
EXEC ( @zdanie)
RETURN @@ERROR
GO
And the error messages I received:
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 18
Incorrect syntax near the keyword 'SET'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 20
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 22
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 25
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 30
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 35
Incorrect syntax near the keyword 'BEGIN'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 41
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 43
Incorrect syntax near the keyword 'SELECT'.
Server: Msg 156, Level 15, State 1, Procedure p_prace_promotora_SHOW_dyna, Line 45
Incorrect syntax near the keyword 'SELECT'.

 
Old September 15th, 2004, 02:09 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hey,

Didn't see this declared: SET @pozycja = 0

Brian
 
Old October 6th, 2004, 02:53 PM
Registered User
 
Join Date: Oct 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, BIT is integer data type 1, 0, or NULL.
Not boolean.

if you use BIT datatype in IF clause -->
not
if @czy_autorzy
but
if @czy_autorzy = 1

and,
you wrong use variable @id_pracownika in dynamic select statement
not
SELECT @zdanie = @zdanie + ' WHERE ( pd.id_pracownika = @id_pracownika) AND ( p.id_pracownika = pd.id_pracownika)'
but
SELECT @zdanie = @zdanie + ' WHERE ( pd.id_pracownika = ' + CAST(@id_pracownika as varchar) + ') AND ( p.id_pracownika = pd.id_pracownika)'



Right script is :

CREATE PROCEDURE p_prace_promotora_SHOW_dyna
    @id_pracownika INTEGER = NULL,
    @czy_autorzy BIT = 0,
    @czy_slowa_kluczowe BIT = 0,
    @czy_recenzenci BIT = 0,
    @znaleziono INTEGER OUTPUT
AS
DECLARE @zdanie VARCHAR( 8000), @pozycja INTEGER

SET @pozycja = 0

SET @zdanie = 'SELECT PD.tytul AS [Tytuł], pd.rodzaj AS [Rodzaj], pd.czy_cd AS [Jest CD], pd.data_obrony AS [Obroniona], pd.ocena AS [Ocena],' +
              ' p.nazwisko + p.imie + p.tytul AS [Promotor]'
IF @czy_autorzy = 1
    SET @zdanie = @zdanie + ', s.nazwisko + s.imie AS [Autor]'

IF ( @czy_slowa_kluczowe = 1)
    SELECT @zdanie = @zdanie + ', sl.slowo_kluczowe AS [SÅ‚owo kluczowe]'

IF ( @czy_recenzenci = 1)
    SELECT @zdanie = @zdanie + ', pr.nazwisko + pr.imie + pr.tytul AS [Recenzent]'

SELECT @zdanie = @zdanie + 'FROM praca_dyplomowa AS pd INNER JOIN pracownik AS p ON p.id_pracownika = pd.id_pracownika'
IF ( @czy_autorzy = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN autorzy AS A ON a.id_pracy = pd.id_pracy'
    SELECT @zdanie = @zdanie + ' INNER JOIN student AS s ON s.id_studenta = a.id_studenta'
END
IF ( @czy_recenzenci = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN recenzenci AS R ON r.id_pracy = pd.id_pracy INNER JOIN pracownik AS PR '
    SELECT @zdanie = @zdanie + 'ON pr.id_pracownika = r.id_pracownika'
END
IF ( @czy_slowa_kluczowe = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN skorowidz AS sk ON sk.id_pracy = pd.id_pracy INNER JOIN slowa_kluczowe AS sl'
    SELECT @zdanie = @zdanie + ' ON sl.id_klucza = sk.id_klucza'
END
SELECT @zdanie = @zdanie + ' WHERE ( pd.id_pracownika = ' + CAST(@id_pracownika as varchar) + ') AND ( p.id_pracownika = pd.id_pracownika)'
IF ( @czy_autorzy = 1)
    SELECT @zdanie = @zdanie + ' AND ( a.id_pracy = pd.id_pracy) AND ( s.id_studenta = a.id_studenta)'
IF ( @czy_recenzenci = 1)
    SELECT @zdanie = @zdanie + ' AND ( r.id_pracy = pd.id_pracy) AND ( pr.id_pracownika = r.id_pracownika)'
IF ( @czy_slowa_kluczowe = 1)
    SELECT @zdanie = @zdanie + ' AND ( sk.id_pracy = pd.id_pracy) AND ( sl.id_klucza = sk.id_klucza)'
EXEC ( @zdanie)
RETURN @@ERROR
GO


 
Old October 6th, 2004, 03:08 PM
Registered User
 
Join Date: Oct 2004
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I forgot on one thing, if @id_pracownika is NULL

Script after review is :

CREATE PROCEDURE p_prace_promotora_SHOW_dyna
    @id_pracownika INTEGER = NULL,
    @czy_autorzy BIT = 0,
    @czy_slowa_kluczowe BIT = 0,
    @czy_recenzenci BIT = 0,
    @znaleziono INTEGER OUTPUT
AS
DECLARE @zdanie VARCHAR( 8000), @pozycja INTEGER

SET @pozycja = 0

SET @zdanie = 'SELECT PD.tytul AS [Tytuł], pd.rodzaj AS [Rodzaj], pd.czy_cd AS [Jest CD], pd.data_obrony AS [Obroniona], pd.ocena AS [Ocena],' +
              ' p.nazwisko + p.imie + p.tytul AS [Promotor]'
IF @czy_autorzy = 1
    SET @zdanie = @zdanie + ', s.nazwisko + s.imie AS [Autor]'

IF ( @czy_slowa_kluczowe = 1)
    SELECT @zdanie = @zdanie + ', sl.slowo_kluczowe AS [SÅ‚owo kluczowe]'

IF ( @czy_recenzenci = 1)
    SELECT @zdanie = @zdanie + ', pr.nazwisko + pr.imie + pr.tytul AS [Recenzent]'

SELECT @zdanie = @zdanie + 'FROM praca_dyplomowa AS pd INNER JOIN pracownik AS p ON p.id_pracownika = pd.id_pracownika'
IF ( @czy_autorzy = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN autorzy AS A ON a.id_pracy = pd.id_pracy'
    SELECT @zdanie = @zdanie + ' INNER JOIN student AS s ON s.id_studenta = a.id_studenta'
END
IF ( @czy_recenzenci = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN recenzenci AS R ON r.id_pracy = pd.id_pracy INNER JOIN pracownik AS PR '
    SELECT @zdanie = @zdanie + 'ON pr.id_pracownika = r.id_pracownika'
END
IF ( @czy_slowa_kluczowe = 1)
BEGIN
    SELECT @zdanie = @zdanie + ' INNER JOIN skorowidz AS sk ON sk.id_pracy = pd.id_pracy INNER JOIN slowa_kluczowe AS sl'
    SELECT @zdanie = @zdanie + ' ON sl.id_klucza = sk.id_klucza'
END
SELECT @zdanie = @zdanie + ' WHERE ( pd.id_pracownika' + isnull(' = ' + CAST(@id_pracownika as varchar), ' is NULL ') + ') AND ( p.id_pracownika = pd.id_pracownika)'
IF ( @czy_autorzy = 1)
    SELECT @zdanie = @zdanie + ' AND ( a.id_pracy = pd.id_pracy) AND ( s.id_studenta = a.id_studenta)'
IF ( @czy_recenzenci = 1)
    SELECT @zdanie = @zdanie + ' AND ( r.id_pracy = pd.id_pracy) AND ( pr.id_pracownika = r.id_pracownika)'
IF ( @czy_slowa_kluczowe = 1)
    SELECT @zdanie = @zdanie + ' AND ( sk.id_pracy = pd.id_pracy) AND ( sl.id_klucza = sk.id_klucza)'
EXEC ( @zdanie)
RETURN @@ERROR
GO







Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL in C# class vs. Dynamic Stored Procedure holf BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 October 26th, 2006 06:45 PM
stored procedure dynamic insert harpua Classic ASP Databases 3 January 21st, 2005 12:50 AM
Dynamic stored procedure bsa SQL Server 2000 2 October 12th, 2004 10:05 PM
Stored Procedure - Dynamic Where Clause Terry_Pino BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 1 July 2nd, 2004 04:39 PM
SQL Stored Procedure... babloo81 SQL Server ASP 1 December 8th, 2003 03:38 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.