|
Subject:
|
help syntax please
|
|
Posted By:
|
khautinh
|
Post Date:
|
8/25/2006 10:16:32 AM
|
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!
|
|
Reply By:
|
som_dutt
|
Reply Date:
|
8/26/2006 2:47:56 AM
|
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
|
|
