CODEWITHSUNDEEP
sqlsql-server
TechGuru
TechGuru

Reputation: 439

Error while running Stored Procedure

I have written one SP which will take the table name as a parameter and select some value and it's count. My SP is given below:-

ALTER PROCEDURE [dbo].[usp_Get_MessageType_And_Count] 
       @table_name varchar(max)  
AS
BEGIN

DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @table_name

DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql =
'select [MessageType],count([MessageType]) as Count
from
(SELECT Split.a.value(''.'+','+ 'VARCHAR(100)'') AS [MessageType]  
 FROM  (SELECT [Message Type],  
         CAST ('+'<M>'+' + REPLACE([Message Type], '+','+', '+'</M><M>'+') + '+'<M>'+' AS XML) AS [MessageType]  
     FROM  ' + @table_name +') AS A CROSS APPLY [MessageType].nodes ('+'/M'+') AS Split(a)) as tab group by [MessageType]'

   EXEC(@sql)  
END

While executing the stored procedure i am getting following error :-

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near '<'.

Please help me as i am not able to find where i am doing wrong. any help will be appreciated. Thanks in advance!!

Upvotes: 0

Views: 79

Answers (2)

mvisser
mvisser

Reputation: 670

Use the below Stored Procedure. I changed the logic when you get the table name, otherwise it will error and as well I made sure that the column "Message Type" also exists, otherwise that will also cause an error. 

ALTER PROCEDURE [dbo].[usp_Get_MessageType_And_Count] 
       @table_name varchar(max)  
AS

BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @ActualTableName AS NVARCHAR(255),
    @sql AS NVARCHAR(MAX)


    SELECT @ActualTableName = t.TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES t
    INNER JOIN INFORMATION_SCHEMA.COLUMNS c
        ON t.TABLE_NAME = c.TABLE_NAME
    WHERE t.TABLE_NAME = @table_name
        AND c.COLUMN_NAME = 'Message Type'

IF (@ActualTableName IS NOT NULL)
BEGIN

   SELECT @sql =
    'SELECT [MessageType],
        COUNT([MessageType]) as CountValue
    FROM
    (
     SELECT Split.a.value(''.'', ''VARCHAR(100)'') AS [MessageType]  
     FROM  (SELECT CAST (''<M>'' + REPLACE([Message Type], '','', ''</M><M>'') + ''</M>'' AS XML) AS [MessageType]  
            FROM ' + @ActualTableName + '
            ) AS A 
     CROSS APPLY [MessageType].nodes (''/M'') AS Split(a)) AS tab 
     GROUP BY [MessageType]'

   EXEC(@sql)  
END

ELSE
BEGIN
    SELECT 'Table/column does not exist' AS [Message]
END


END

Upvotes: 1

Jatin Patel
Jatin Patel

Reputation: 2104

I think you are looking for something like this:

ALTER PROCEDURE [dbo].[usp_Get_MessageType_And_Count] 
       @table_name varchar(max)  
AS
BEGIN

DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @table_name

DECLARE @sql AS NVARCHAR(MAX)
    SELECT @sql =
'select [MessageType],count([MessageType]) as Count
from
(SELECT Split.a.value(''.'',''VARCHAR(100)'') AS [MessageType]  
 FROM  (SELECT [Message Type] AS [Message Type CSV],  
         CAST (''<M>'' + REPLACE([Message Type], '','',''</M><M>'') + ''</M>'' AS XML) AS [MessageType]  
     FROM  ' + @table_name +') AS A CROSS APPLY [MessageType].nodes (''/M'') AS Split(a)) as tab group by [MessageType]'

   EXEC(@sql)  
END

Hope this helps.

Upvotes: 1

Related Questions