Problem with Stored Procedures (To JamesLean)

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

Thanks

 

[JamesLean]

A-ha!
Your SQL string is 105 characters long but your variable @sql is only 100 chars. The last 'abrev' is being chopped off the statement so the SQL string ends '.....ORDER BY '.

This is what is causing the error.

Just change the variable declaration to varchar(110).