error building dynamic sql in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error building dynamic sql in stored procedure

Author

Topic

paaf64
Starting Member

5 Posts

Posted - 2004-09-15 : 10:38:42

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

Thank you for any suggestion how to solve this!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 10:54:43

IF ( @czy_slowa_kluczowe) can't be compared like that
u must use:
IF ( @czy_slowa_kluczowe = 1) or IF ( @czy_slowa_kluczowe = 0)

Go with the flow & have fun! Else fight the flow

paaf64
Starting Member

5 Posts

Posted - 2004-09-15 : 10:58:16

Thank you very much! It worked!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 11:14:48

if i uderstand my Slavic correct

this is a book review sproc, isn't it? just curious if i understand correctly

Go with the flow & have fun! Else fight the flow

paaf64
Starting Member

5 Posts

Posted - 2004-09-15 : 11:26:19

Not quite, but close...
The diploma papers warehousing application for technical university. We can improve your Polish easily exploiting your Slavonic background

Thank you once more for saving my time and workarounding!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 11:47:22

hey no problem

but i was close...jupi...

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 12:42:47

why don't just return all the columns in a regular (non-dynamic) select statement, and let the users decide which ones they want to use or ignore? If they know how to access a VIEW on a SQL server, they should know how to select/display only the columns they want.

IMHO, you are making things overcomplicated and very difficult to debug/maintain in the future.

- Jeff

paaf64
Starting Member

5 Posts

Posted - 2004-09-15 : 13:05:15

The idea is to isolate the server side access by stored procedures API available to the clients applications.
Any suggestions?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-15 : 13:39:11

that's fine, but why not just return all columns? they can just ignore what they don't need. This huge bit of dynamic SQL in this stored procedure then becomes just a simple SELECT with a few joins.

if you use dynamic SQL in a stored proc, the logon executing the proc must have access rights to the tables that the dynamic SQL is accessing. whereas if you don't use dynamic SQL, you can lock down permissions on the objects in your DB for better security, if that is what you are after.

- Jeff

paaf64
Starting Member

5 Posts

Posted - 2004-09-15 : 14:13:19

Yes, the main idea is to separate both worlds: server's db and client's application.
But unfortunately, for my dynamic sql, you are correct with the access rights and unneccessary burden concerned with them. But that also means that I will have a lot of additional selects for every parameter in the project...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources