No logic / Who can explain this :)

[SilentWings]

Hi ppl,

I am creating a stored procedure which contains se following sql: "SELECT abrev , dom FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev"

This query runs ok in then sql query analyser. When I run it in then stored procedure I have the following error:
"Line 1: Incorrect syntax near 'BY'"

What is appening here.
Can some brain explain it to me and give me a solucion?
Thanks...

 

[JamesLean]

Can you post the full code for your SP. The error must be coming from somewhere else as there is nothing wrong with that SELECT statement.

 

[SilentWings]

Hi James

My sp code is the following:

CREATE Procedure daestadobackup
(@pdia int
)
As
DECLARE @sql varchar(100)

IF (@pdia=1)
BEGIN
set @sql='SELECT abrev , dom FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev'
END

IF (@pdia=2)
BEGIN
set @sql='SELECT abrev , seg FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev'
END

exec(@sql)
GO

When I run teh sql statment "SELECT ... ORDER BY abrev" it works perfectly. When I run " Exec daestadobackup 1" it returns an error.

 

[sunila7]

Hi,

Probelm is with @Sql Declaration.... u assigned only 100 char length ur SQL is more than that
try this.....

CREATE Procedure daestadobackup
(@pdia int
)
As
DECLARE @sql varchar(200)

IF (@pdia=1)
BEGIN
set @sql='SELECT abrev , dom FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev'
END

IF (@pdia=2)
BEGIN
set @sql='SELECT abrev , seg FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev'
END

exec(@sql)
GO

And to do this u need use a dynamic SQL... u could do it this way...

CREATE Procedure daestadobackup
(@pdia int
)
As

IF (@pdia=1)
BEGIN
SELECT abrev , dom FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev
END

IF (@pdia=2)
BEGIN
SLECT abrev , seg FROM lojas INNER JOIN estadobackups ON lojas.loja = estadobackups.loja ORDER BY abrev
END


GO

Hope it helps


Sunil

 

[SilentWings]

Thanks very much.

The problem is solved