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'.

Hey,

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

Brian

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

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