Error in stored procedure - 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

Database Design and Application Architecture

Error in stored procedure

Author

Topic

Gyte
Starting Member

23 Posts

Posted - 2007-02-09 : 03:11:22

Hello, I'm migrating stored procedures from SQL-Server 2000 to SQL-Server 2005 and one procedure generates an error. See the below procedure. Could someone help me with this?

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE dbo.fl_GetDataABReport22
@varTable varChar(5),
@sStartSQL varChar(10),
@sEndSQL varChar(10),
@SalesID varChar(1000)
AS

DECLARE @SQL VarChar(1000)

SELECT @SQL = ""
SELECT @SQL = @SQL + "SELECT COUNT(Expr1) "
SELECT @SQL = @SQL + "FROM ( "
SELECT @SQL = @SQL + "SELECT Rcyclusnumber + ' ' + CAST(Rclientnumber AS nvarchar) AS Expr1 "
SELECT @SQL = @SQL + "FROM tblReport" + @varTable + " "
SELECT @SQL = @SQL + "WHERE Rdate >= '" + @sStartSQL + "' "
SELECT @SQL = @SQL + "AND Rdate <= '" + @sEndSQL + "' "
SELECT @SQL = @SQL + "AND RsalesID = " + @SalesID + " "
SELECT @SQL = @SQL + "GROUP BY Rcyclusnumber + ' ' + CAST(Rclientnumber AS nvarchar) "
SELECT @SQL = @SQL + "HAVING COUNT(Rcyclusnumber + ' ' + CAST(Rclientnumber AS nvarchar)) = 1 "
SELECT @SQL = @SQL + ") AS A"

Exec(@SQL)
GO

Msg 1038, Level 15, State 4, Procedure fl_GetDataABRapport22, Line 15
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 03:19:33

why are you using double quote " ? use single quote ' to enclose a string

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 03:20:13

are you sure the existing code runs in SQL 2000 ?

Kristen
Test

22859 Posts

Posted - 2007-02-09 : 04:38:29

Put a PRINT or SELECT statement before the EXEC, and then try running the generated code; debug that code as necessary, then feed the changes back into the procedure.

I would also advise you to parametrise this and use sp_ExecuteSQL instead of EXEC which will improve the likelihood that this query is cached and, with the exception of

FROM tblReport" + @varTable + "

will remove the risk of SQL Injection.

Kristen

Gyte
Starting Member

23 Posts

Posted - 2007-02-09 : 05:45:44

Thank you all. I used your suggestions and it works just fine now.
I altered the "SELECT" part like below.

SELECT @SQL = ''
SELECT @SQL = @SQL + 'SELECT COUNT(Expr1) '
SELECT @SQL = @SQL + 'FROM ( '
SELECT @SQL = @SQL + 'SELECT Rcyclusnumber + '' '' + CAST(Rclientnumber AS nvarchar) AS Expr1 '
SELECT @SQL = @SQL + 'FROM tblReport' + @varTable + ' '
SELECT @SQL = @SQL + 'WHERE Rdate >= ''' + @sStartSQL + ''' '
SELECT @SQL = @SQL + 'AND Rdate <= ''' + @sEndSQL + ''' '
SELECT @SQL = @SQL + 'AND RsalesID = ' + @SalesID + ' '
SELECT @SQL = @SQL + 'GROUP BY Rcyclusnumber + '' '' + CAST(Rclientnumber AS nvarchar) '
SELECT @SQL = @SQL + 'HAVING COUNT(Rcyclusnumber + '' '' + CAST(Rclientnumber AS nvarchar)) = 1 '
SELECT @SQL = @SQL + ') AS A'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources