1

I am having a problem with my sql function. The following function operates as expect but when I substitute, it only returns one row:

CAST(t.ColumnName as nvarchar(100)) 

for:

REPLACE(CAST(t.ColumnName as nvarchar(100)), 'ID', 'Id')

What is wrong? Here is my calling code:

PRINT dbo.CreatePocoFromTable('dbo.OnePoundFish')

Here is the function itself:

CREATE FUNCTION [dbo].[CreatePocoFromTable]
    (@SchemaAndName nvarchar(200))
RETURNS Nvarchar(MAX) AS
BEGIN
/* Example call:

PRINT dbo.CreatePocoFromTable('dbo.OnePoundFish')

*/

DECLARE @TableName NVARCHAR(100) = SUBSTRING(@SchemaAndName , CHARINDEX('.', @SchemaAndName ) + 1, LEN(@SchemaAndName ))   
DECLARE @TableSchema NVARCHAR(100) = SUBSTRING(@SchemaAndName , 1, CHARINDEX('.', @SchemaAndName ) - 1)  
DECLARE @result Nvarchar(max) = ''

SET @result = @result + 'using System;' + CHAR(13) + CHAR(13) 

IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13) 
END

SET @result = @result + 'public class ' + @TableName + CHAR(13) + '{' + CHAR(13) 

SET @result = @result + '#region Instance Properties' + CHAR(13)  

SELECT @result = @result + CHAR(13) 
    -- BL/DAL/DataRepository Entity attributes
    + '[DataField("' + t.ColumnName + '")]' + CHAR(13)
    + ' public ' + t.ColumnType + ' ' + CAST(t.ColumnName as nvarchar(100)) + ' { get; set; } ' + CHAR(13) 

FROM
(
    SELECT  
        c.COLUMN_NAME   AS ColumnName 
        , CASE c.DATA_TYPE   
            WHEN 'bigint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
            WHEN 'binary' THEN 'Byte[]'
            WHEN 'bit' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END            
            WHEN 'char' THEN 'String'
            WHEN 'date' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime2' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetimeoffset' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                    
            WHEN 'decimal' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                    
            WHEN 'float' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Single?' ELSE 'Single' END                                    
            WHEN 'image' THEN 'Byte[]'
            WHEN 'int' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
            WHEN 'money' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                
            WHEN 'nchar' THEN 'String'
            WHEN 'ntext' THEN 'String'
            WHEN 'numeric' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                            
            WHEN 'nvarchar' THEN 'String'
            WHEN 'real' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Double?' ELSE 'Double' END                                                                        
            WHEN 'smalldatetime' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'smallint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END            
            WHEN 'smallmoney' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                                        
            WHEN 'text' THEN 'String'
            WHEN 'time' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                   

            WHEN 'timestamp' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'tinyint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                                
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION 
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA  
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + '#endregion Instance Properties' + CHAR(13)  

SET @result = @result  + '}' + CHAR(13)

IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + CHAR(13) + '}' 
END

  RETURN @Result
END

Thanks :)

2
  • How is REPLACE, either working or not, related to your function? Commented Nov 17, 2012 at 19:34
  • Because the desire outcome is like this: [DataField("ColumnID")] public Int32 ColumnId { get; set; } -- where the one format ends with upper case ID and the other proper case -- also it was the REPLACE keyword that prevented it from enumerating more than one row -- So, it's pretty MAJOR! Commented Nov 17, 2012 at 19:49

2 Answers 2

1

only the interesting part ...

SELECT @result = @result + CHAR(13) 
    -- BL/DAL/DataRepository Entity attributes
    + '[DataField("' + CAST(REPLACE(t.ColumnName , 'ID', 'Id') as nvarchar(100))   + '")]' + CHAR(13)
--    + '[DataField("' + t.ColumnName  + '")]' + CHAR(13)

    + ' public ' + t.ColumnType + ' ' + CAST(t.ColumnName as nvarchar(100)) + ' { get; set; } ' + CHAR(13) 

FROM
Sign up to request clarification or add additional context in comments.

3 Comments

Bizarre - that's the only variation I didn't try. I'm tired but I was thinking that it wouldn't matter whether I did the replace before the cast or the other way about.
CAST ... AS VARCHAR should be CAST AS VARCHAR(some length). See sqlblog.org/blogs/aaron_bertrand/archive/2009/10/09/…
I knew what he meant - The resulting function casts as NVARCHAR(100) - just like the original snippet
0

Is your sql server set to be case sensitive? If not (and I believe it's not by default) then replace based on different case of the same word will not work.

See this link to change the collation to a case sensitive one How do I change SQL Server 2005 to be case sensitive?

1 Comment

I am unwilling to change the collation on the whole database. At what level do apply the collation. For example, the following runs but does not give the desired result: PRINT dbo.CreatePocoFromTable('dbo.OnePoundFish') COLLATE Latin1_GENERAL_CS_AS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.