I have the following codes, when running I got the syntax error:
"Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'output'."

Please help me, what did I do wrong or miss?

CREATE PROCEDURE Cmd_ConsoleCounter
  (
@TableName varchar(255),
@Col varchar(255),
@value varchar(255) ,
@RecCount int = null output
  )
AS

    DECLARE @SQLStatement varchar(255)

    SELECT @SQLStatement = 'SELECT Count(*) FROM ' + @TableName + ' Where ' + @col + ' = ' + CHAR(39) + @value + CHAR(39)+ ' output '

-- EXEC @RecCount=@SQLStatement
 EXEC (@SQLStatement)
GO


Thanks a lot!

Hi khautinh!

The output keyword added in your statement is producing error-

SELECT @SQLStatement = 'SELECT Count(*) FROM ' +
@TableName + ' Where ' + @col + ' = ' + CHAR(39) +
@value + CHAR(39)+ ' output '

There is no statement like this. If you are keen to get the rowcount, you can use @@rowcount function. Something like this -

@RecCount=@@rowcount

So, remove the output statement and based on your procedure, the final procedure would be
(With some other modifications) -

--------------------------------

Create PROCEDURE Cmd_ConsoleCounter

@TableName varchar(255),
@Col varchar(255),
@value varchar(255) ,
@RecCount int output
AS

DECLARE @SQLStatement varchar(255)

SELECT @SQLStatement = 'SELECT * FROM ' + @TableName + ' Where ' + @col + ' = ' + CHAR(39) + @value + CHAR(39)

EXEC (@SQLStatement)
SET @RowCount=@@ROWCOUNT
GO

--------------------------------

This would select the table and also tell you no. of rows selected. The modification are as follows -

1. Parameters were in parenthesis.Used only with UDFs. No need to place them.

2. @RecCount int = null output : No need to intialize with null. @@ROWCOUNT if does not selects any row, 0 would be placed. One more thing null and 0 have lots of differences. (u must be known of it)

3. output at the end of select statment is syntax error.

4. Adding Set @RowCount=@@ROWCOUNT would output the no. of rows selected.

That's it. Try it out and do not forget to reply whether it worked or not.


- Som Dutt
---------------------------
http://doeaccpapers.com
http://somdutt.blogspot.com