Hi
I suggest you print out the statement @sql and debug it in Query Analyser.
"Joško Šugar" <josko@netgen_makniOvo.hr> wrote in message
news:7512.41bdcba7.c66fd@hubble...[color=blue]
> On this site:
>
http://www.sommarskog.se/dynamic_sql.html[/color]
And what an excellent site it is!
[color=blue]
>
> I have found an example how to use cursor with dynamic SQL:
> DECLARE @my_cur CURSOR
> EXEC sp_executesql
> N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN
> @my_cur',
> N'@my_cur cursor OUTPUT', @my_cur OUTPUT
> FETCH NEXT FROM @my_cur
>
>
> But when I tried to do this:
>
> IF (@Naziv <> '')
> SET @sql_where = @sql_where + N' AND Naziv LIKE ' + @Naziv
>[/color]
I would expect quotes around your strings say:
SET @sql_where = @sql_where + N' AND Naziv LIKE ''' + @Naziv + '%'''
[color=blue]
> IF (@Funk <> '')
> SET @sql_where = @sql_where + N' AND Funkcija LIKE ' + @Funk
>
> IF (@Mj <> '')
> SET @sql_where = @sql_where + N' AND NazivMjesta LIKE ' + @Mj
>
> IF (@Drz <> '')
> SET @sql_where = @sql_where + N' AND (drzava1 LIKE ' + @Drz +
> ' OR drzava2 like ' + @Drz + ' OR drzava3 LIKE ' + @Drz + ')'
>
>
> DECLARE @CursSearch CURSOR
>
> SET @sql = N'SET @CursSearch = CURSOR FOR
> SELECT CvorID, NadCvorID,
> IzvorisniCvorID, Naziv, TipCvora,
> NasljednaLinija, Funkcija, NazivMjesta,
> drzava1, drzava2, drzava3
> FROM dbo.Pretrazivanje
> WHERE NasljednaLinija LIKE @NasljednaLinija'
> + @sql_where + N'; OPEN @CursSearch'
>
> EXEC sp_executesql @sql, N'@CursSearch CURSOR OUTPUT',
> @CursSearch OUTPUT
>
>
>
> ...by fetching cursor i got this message:
> The variable '@CursSearch' does not currently have a cursor allocated to
> it.
>
>
> Can anybody tell me what i did wrong?[/color]
John
