Select Top with @param - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select Top with @param

Author

Topic

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 09:50:17

Hello,

If i use the param n_filhos in the select the output is Filhos0,
but if i use in the Select Filhos0 instead of n_filhos the output
is correct: 0,0600000.
Why this happens?
Thanks for helping.

This are the parameters that the query receives:

param n_filhos = Filhos0
param ordenado = 800
param estado = S

SELECT TOP 1 @n_filhos
FROM TABELAIRS t
WHERE (t.Valor > @ordenado) AND (t.Tabela = @estado)

TABELAIRS

Id Tabela Linha Valor Filhos0 Filhos1
2 S 1 540,00000000 0,00000000 0,00000000
3 S 2 570,00000000 0,01000000 0,00000000
4 S 3 610,00000000 0,02000000 0,00000000
5 S 4 650,00000000 0,03000000 0,01000000
6 S 5 690,00000000 0,04000000 0,02000000
7 S 6 770,00000000 0,05000000 0,04000000
8 S 7 870,00000000 0,06000000 0,05000000
9 S 8 950,00000000 0,07000000 0,06000000
10 S 9 1010,00000000 0,08000000 0,07000000
11 S 10 1080,00000000 0,09000000 0,08000000
12 S 11 1160,00000000 0,10000000 0,09000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:54:11

you need to use dynamic sql if you need value of column whose name you pass via param. try below

EXEC ('SELECT TOP 1 '+@n_filhos+' FROM TABELAIRS t
WHERE (t.Valor > @ordenado) AND (t.Tabela = @estado)')

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 10:04:40

That works on SQL server 2005?
Because i'm getting the following error:
"Unexpected 'EXEC' in SQL Statement".

Thanks for the fast answer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:07:46

wat about this?

EXEC ('SELECT TOP 1 '+@n_filhos+' FROM TABELAIRS t
WHERE (t.Valor > '+@ordenado+') AND (t.Tabela = '+ @estado+')')

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 10:20:22

It's getting better.

But now this:

" Error in query: Invalid column name 'S' ".

That is correct i don't have a column name S, but
S is the a search param!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:24:41

show your full query please

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 10:27:32

EXEC ('SELECT TOP 1 '+@n_filhos+'
FROM OSUSR_U6O_TABELAIR t
WHERE (t.Valor > '+@ordenado+')
AND (t.Tabela = '+@estado+')')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:30:07

i dont think this is full query as i still cant find column S as specified by error.

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 10:32:41

One thing i notice, if the query is only:

EXEC ('SELECT TOP 1 '+@n_filhos+'
FROM OSUSR_U6O_TABELAIR t
WHERE (t.Valor > '+@ordenado+')')

the query returns no errors and the output is correct, but with

EXEC ('SELECT TOP 1 '+@n_filhos+'
FROM OSUSR_U6O_TABELAIR t
WHERE (t.Valor > '+@ordenado+')
AND (t.Tabela = '+@estado+')')

it returns that error!
But i need the full validation with the AND.

This query is used in the advanced query widget in Outsystems.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:39:03

i think i got it . try this

EXEC ('SELECT TOP 1 '+@n_filhos+' 
FROM OSUSR_U6O_TABELAIR t
WHERE (t.Valor > '+@ordenado+') 
AND (t.Tabela = '''+@estado+''')')

Syslog
Starting Member

6 Posts

Posted - 2008-11-06 : 10:40:15

Works like a charm!!

Thank you very much for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:56:13

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources